Oracle Format Procedure In SAS

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.5
Alice      F            13           56.5        84
Barbara    F            13           65.3        98
Carol      F            14           62.8        102.5
Henry      M            14           63.5        102.5
Run;

/* 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.05
2= 0.1
3= 0.15;
Run;
Data medi;
Input pid week: $wk
Dose: ds;
Cards;
100        W1        1
101        W1        2
100        W2        2
101        W2        1
100        W3        2
101        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        1
101        W1        2
100        W1        2
101        W2        3
100        W2        1
101        W2        3
;
Proc print data = medi 1;
Run;

/* Numeric to numeric conversion (loading) based on ranges */

Eg:

Proc format;
In value sal low – 1000 = 1000
1000 < - < 3000 = 3000
3000-5000 = 5000
5000 <- high = _ same_;
Run;
Data emp;
Input eid salary: sat;
Cards;
100         500
234         700
445         1000
456         2300
345         1690
478         4000
567         5600
678         7900
908         8000
389         9800
876         12000
;
Proc print data = emp;
Run;

Capture

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 $ :h
Color $ : b : levels $ dreg;
Format level n drugnn;
Cards;
100         1              W            n             1
101         2              b            s             2
102         F              W            n             3
103         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              L
101         2              H
102         1              M
103         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 inr
Low – high = ’00,00,00,00,ooo’;
Run;
Data company;
Input cname $ invest;
Cards;
Satyam 1234908765
Tcs          5600005600
Wipro    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 rpt
Low -< 150 = ‘999 normal stage’
150 - <180 = ‘999 control stage’
180 – High = ‘999 uncontrol stage’;
Run;
Data svisit;
Input pid sbp;
Cards;
100         189
101         145
102         190
103         160
104         155
105         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 =gender
w1 = week
d1 =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 as
Select * 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’ = Col5mg
VALUE 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