Format Procedure
It is a SAS BASE procedure format procedure can be used to create user defined in formats and formats.
SAS/TOOL KIT
This is one of the products in SAS BASE. It is supporting to create user defined formats and in formats.
IN VALUE STATEMENT
Can be used to create user defined in formats for data reading and loading
CONVERSION: (for reading)
Character to character
F for female
M for male
Numeric to numeric
1 – 0.05
2 – 0.1
3 – 0.15
Character to numeric
L – 0.05
M – 0.1
H – 0.15
VALUE STATEMENT
Can be used to create user defined formats for reporting
Character to character
Numeric to character
USER DEFINED INFOEMATS CREATION:
- Character to character
RULES FOR INFORAMT ANSD FORMAT:
- We can give up to 16 characters
- Starts with letter or underscore(_) and don’t end with number
- It allows numbers and character
- If it is character to character conversion, informat & format starts with ‘$’ symbol
Desired to gain proficiency on SAS? Explore the blog post on SAS Training to become a pro in SAS.
/* to load the data */ /* character to character */
Eg:
Proc format;In value $gn ‘F’ = ‘female’‘M’ = ‘male’;Run;Data demo;Input pname $ gender $ gn;Cards;Alfred M 14 69 112.5Alice F 13 56.5 84Barbara F 13 65.3 98Carol F 14 62.8 102.5Henry M 14 63.5 102.5Run;
/* Numeric to numeric (dose) character to character (week) */
Eg:
Proc format;In value $wk ‘W1’ = ‘week1’‘W2’ = ‘week1’‘W3’ = week3’In value ds 1= 0.052= 0.13= 0.15;Run;Data medi;Input pid week: $wkDose: ds;Cards;100 W1 1101 W1 2100 W2 2101 W2 1100 W3 2101 W3 3;
TO CREATE FORMATS FOR REPORTING:
- Character to character reporting
- Numeric to character reporting
Eg:
Proc format;Value $wk ‘W1’ = ‘week1’‘W2’ = ‘week1’;Value dg 1= ‘col 5mg’2= ‘col 10mg’3= ‘col 15mg’;Run;Data medi 1;Input stno week $ drug;format week $ wk drug dg;Cards;100 W1 1101 W1 2100 W1 2101 W2 3100 W2 1101 W2 3;Proc print data = medi 1;Run;
/* Numeric to numeric conversion (loading) based on ranges */
Eg:
Proc format;In value sal low – 1000 = 10001000 < - < 3000 = 30003000-5000 = 50005000 <- high = _ same_;Run;Data emp;Input eid salary: sat;Cards;100 500234 700445 1000456 2300345 1690478 4000567 5600678 7900908 8000389 9800876 12000;Proc print data = emp;Run;
Proc fromat;Invalue $ h ‘F’ = ‘female’‘m’ =’male’‘1’ = ‘female’‘2’ = ‘male’;
In value $ b ‘b’ = ‘black’‘w’ = ‘white’;Value $n ‘n’ = ‘normal level’‘s’ = ‘serious’;Value nn 1= ‘cipla 5mg’2 = ‘cipla 7mg’3 = ‘cipla 9mg’;Data * demo;Input pif genders $ :hColor $ : b : levels $ dreg;Format level n drugnn;Cards;100 1 W n 1101 2 b s 2102 F W n 3103 M b s 1;Run;Proc print data = dd;Run;
Permanent infromat and format creation:
Library option:
Can be used to store formats in required library default value of the library option is work
Fmt search option:
It is a global option it indicates format default format library is work
Fmt search options:
It is a global option it indicates format library is work
Eg:
Proc format library = sas user ;In value ds ‘L’ = 0.05‘M’ = 0.1‘H’ = 0.15;Value gn 1 = ‘female’2 = ‘male’;Run;Options fmt search = (sas user);Data medicine;Input stno gender does : ds;Cards;100 1 L101 2 H102 1 M103 2 L;Proc print data = medicine;Run;
Template:
Is also a type of format it is used for reporting using templates, we can join special characters in data value and join the text for reporting
Picture statement:
Can be used to create templates
Eg:
Proc format;Picture inrLow – high = ’00,00,00,00,ooo’;Run;Data company;Input cname $ invest;Cards;Satyam 1234908765Tcs 5600005600Wipro 2300034500;Proc print data = company;Format invert inr;Run;
Zero (0):
Is a reserved digit for picture statement one indicate to one digit for picture statement
/* To join text for reporting */
Eg:
Proc format;Picture rptLow -< 150 = ‘999 normal stage’150 - <180 = ‘999 control stage’180 – High = ‘999 uncontrol stage’;Run;Data svisit;Input pid sbp;Cards;100 189101 145102 190103 160104 155105 140;Proc print data = svisit;Format sbp rpt;Run;
Nine ‘9’:
If we join text for reporting we can indicate each digit in data value use nine ‘9’.
- If we want to load existed data in required format, then we should use define in format technique using with input and put functions
Input function:
Can be used to convert any format (data value) into numeric format for loading input function is working based on in format
Put function:
Can be used to convert any data value in to character format it is working based on format technique
Syntax:
Input (variable name, in format);
Put( variable name, format);
- Put and input function belongs to in format technique here put function requires format but internally works like a format.
Raw data:
Stno | Gender | Week | Drug |
100 | 1 | W1 | L |
101 | 2 | W1 | M |
103 | 1 | W1 | L |
100 | 1 | W2 | M |
101 | 2 | W2 | H |
Eg:
Proc sql;Connect to excel(path = ‘d:\ don.xls’);Create table demo as select * from connection to excel(select * from [sheet 1$]);Quit;Proc sql;Describe table demo;Quit;
/* user defined in format and format creation */
Proc format;In value $ wk ‘W1’ = ‘week1’‘W2’ = ‘week2’;In value ds ‘L’ = 0.05‘M’ = 0.1‘H’ = 0.15;Value gn 1 = ‘female’2 = ‘male’;
/* loading data in required format */
Data demo;Set demo;g1 = put(gender, gn);w1 = input(week, $wk);d1 = input(drug, ds)drop gender week drug;rename g1 =genderw1 = weekd1 =drug;run;proc print data = demo width = minimum;run;
RAW DATA
Stno | Gender | Visit | Dreg |
100 | F | 1 | L |
101 | M | 1 | M |
103 | F | 1 | L |
100 | F | 2 | M |
101 | M | 2 | H |
103 | F | 2 | H |
[divider style="clear"]
FOR LOADING FOR REPORT
F ='female' 1 - visit 1
M ='male 2 - visit 2
DRUG
L = Col2mg
'M = Col4mg
H = Col5mg
Proc sql;Connect to excel(Path =’d:\don.xls’);Create table sam asSelect * from connection to excel(select * from [sheet 2 $];Disconnect from excel;Quit;Proc sql;Describe table sam;Quit;Proc format;Invalue $gn ‘F’= ‘female’‘M’ = ‘male’‘f’ = ‘female’‘m’ = ‘male’;Invalue $ds ‘L’ = Col2mg‘M’ = Col4mg‘H’ = Col5mgVALUE VS 1 =’ visit1’2 = ‘visit 2’Run;Data sam 1;Set sam;G1 = input (gender, $gn);D1 = input (drug, $ds);Drop gender drug;Rename g1 = gender d1 =drug;Run;Proc print data = sam1 width = minimum;Format visit vs;Run;
For indepth knowledge on SAS, click on below
- Normalization in SAS
- SQL Aggregations & DataSet(Row & colummn operations) in SAS
- SQL Statements in SAS
- Working with Macro Variable in SAS
- SAS Interact With Oracle
- SAS Interview Questions & Answers