Formats to the Rescue

1
Formats to the Rescue

• Gary McQuown
• Data and Analytic Solutions Inc.
• Fairfax, VA.

2
Combining Data
MERGE
A
SQL JOIN
Key
C
B
3
Combining Data
INDEX
A
C
B
4
Combining Data
HASHING
A
C
B
5
Combining Data
A
B
Key W
C
FORMATS
D
Key Z
Key Y
Key X
C
G
E
I
H
F
J
6
Issues

• Sorts or Index are Impractical
• Many Small Tables
• Frequently Used Process
• Efficiency
• Ease of Use

7
Formats

• Table lookup
• Associate one value with another
• format date date9. 09OCT2006
• format zip cityz. New York

8
put ( start , myfmt. )

• First Name, Last Name, DEA , EIN, SSN, DOB,
  Specialty, Complaints, Office Address, Home
  Address, States Licensed, Drug Class Allowed,
  Sanctions, etc.
• Drug Names, Drug Category, Drug Class
• Latitude, Longitude, City, County, State

9
Advantages

• No Sorting of Primary File
• Sort Smaller Files
• Unique Start Values

10
Advantages

• Efficient
• (where( put(start, myfmt.) 1 ))
• Reads only selected rows

11
Advantages

• Flexible
• var put(start, myfmt.)
• if put(start, myfmt.) XX
• format (start, myfmt.)

12
Advantages

• Reusable
• (library library) Catalogs
• Reduces Coding (replaces if then !)
• Easily Update

13
Easy to Create

• Numeric
• proc format
• value SexNum
• 1Male
• 0Female
• run

• Character
• proc format
• value SexChar
• 1Male
• 0Female
• run

14

• proc sort data FOO nodupkey by FROM_VAL run
• data MYFMT (keep fmtname hlo label start type)
• retain fmtname MYFMT
• type N
• set FOO
• end lastrec
• start FROM_VAL
• label TO_VAL
• output
• if lastrec then
• do
• hlo O
• label 0
• output
• end
• run
• proc format cntlin MYFMT librarylibrary run

15

• FMTNAME name
• TYPE C N I J P
• START values to be converted.
• LABEL values to become.
• HLO H L O

16

• proc sort
• data FOO nodupkey
• by FROM_VAL
• run

17

• data MYFMT
• (keep fmtname hlo
• label start type)
• retain fmtname MYFMT
• type N
• set FOO
• end lastrec
• start FROM_VAL
• label TO_VAL
• output

18

• if lastrec then
• do
• hlo O
• label 0
• output
• end
• run

19
Other, HI and LOW
Dept_Num put(EMPID, Department_Number.) Dept_Na
me put(Dep_num, Department_Name.) Grade_Level
put(EMP_ID, Grade_Level.) Full_Name
put(EMPID, Full_Name.)


Dept_Name Full_Name EMP_ID Dept_Num Grade_Level
IT John Doe 13579 3 7
Management 24680 24680 2 2
Unknown Unknown 24680 0 0
20
Other, HI and LOW

Dept_Num put(EMPID, Department_Number.) Dept_Na
me put(Dep_num, Department_Name.) Grade_Level
put(EMP_ID, Grade_Level.) Full_Name
put(EMPID, Full_Name.)


Dept_Name Full_Name EMP_ID Dept_Num Grade_Level
IT John Doe 13579 3 7
Management 24680 24680 2 2
Unknown Unknown 24680 0 0
21
Other, HI and LOW
Dept_Num put(EMPID, Department_Number.) Dept_Na
me put(Dep_num, Department_Name.) Grade_Level
put(EMP_ID, Grade_Level.) Full_Name
put(EMPID, Full_Name.)


Dept_Name Full_Name EMP_ID Dept_Num Grade_Level
IT John Doe 13579 3 7
Management 24680 24680 2 2
Unknown Unknown 24680 0 0
22
OPTIONS

• FMTSEARCH to search Libraries
• NOFMTERR use original values if the format does
  not exist

23
Formats

• Merging
• Matching
• Sub Setting
• New Variables
• Data Cleaning
• Reporting
• Analysis

Large Tables Many Tables Many Key
Fields Flexibility Efficiency Easy to Use
24
Questionsand Comments
25
Thank you

• Gary McQuown
• Data and Analytic Solutions Inc.
• Fairfax, VA.
• 703-628-5681
• http//www.DASconsultants.com
• SAS is a Registered Trademark of the SAS
  Institute, Inc. of Cary, North Carolina.