SAS: Organizing data through Proc Format

Proc Format can be used to group categorical variables or numeric values for reporting.  The following examples show the coversion of sex, age, and id variables into desired format. Method 1 uses put and input statements and creates new variables, while method 2 use format statement to just format the existing variables.

• Need $ in the format name for the existing character variable. eg. $gender
• Need to put . after the format name when applying the format. eg. gender = put(sex, $gender.); format age agerange. sex $gender.;

/**** Proc Format ****/
proc format;
value
$gender
‘M’ = ‘Male’
‘F’ = ‘Female’
;
value
agerange
1 – 30 = ‘le 30’
30<-40 = '31 to 40'
40<-50 = '41 to 50'
50<-60 = '51 to 60'
60<-100 = 'gt 60'
other = 'Missing'
;
run;
options fmtsearch = (work.formats);

Method1:

data admit;
set sasuser.admit;
agecatgory = put(age, agerange.);
gender = put (sex, $gender.);
numericid = input(id, 4.);
run;

Method2:

data admit;
set sasuser.admit;
format age agerange. sex $gender.;
run;

Character to Numeric, using INVALUE.
Example: convert character gpa to numeric gpa.

/*** calculate the ESL grade points that needs to be backed out from the GPA ***/
/** format grade to index **/
proc format ; 
invalue   /* UES INVALUE, NOT VALUE */
$grade
'A+' = 9
'A'= 8
'B+'= 7
'B'= 6
'C+' = 5
'C' = 4
'D+' = 3
'D' = 2
'E' = 1 
'F' = 0
'P' = 0
;
run;
proc options  option=fmtsearch;run;
data backgpa;
set pes;
array crs{3} esl1000 esl1010 esl1015  ;
array ncr{3} esl1000ncr esl1010ncr esl1015ncr  ;
array cr{3} esl1000cr esl1010cr esl1015cr  ;
array back{3} esl1000bo esl1010bo esl1015bo;
do i =1 to dim(crs);
if crs{i} ne '' and ncr{i} ne 'NCR' then  back{i} =  input(crs{i}, $Grade.) * input(cr{i}, 8.);
end;
drop course1-course20 gr1-gr20 credit1-credit20 ncr1-ncr20 i;
run;

SAS: Access permanent format

If you see “Format $var1 was not found or could not be loaded.” message in the log, use format search options to access the permanent format to run the program.

options fmtsearch = (libname.dataset);

eg:

options fmtsearch = (fmt_pes.formats);

 

SAS output to date stamped Excel file

Sometimes people were asking for data and you just provided it to them. If it is an annual completed static data, you don’t have to do the date stamp to keep a record on when you retrieve the data from the system.  However, if it is an operational ongoing data with day-to-day changes and also the data is not officially published for the year,  you might want to keep a record of the date for reference.

The SAS code here basically provides a date format that you want to show on the Excel file name.  The format code itself is quite complicated, but you don’t have to remember it at all. Just copy and paste. There are many variations to it.  Then use macro functions %unquote and %sysfunc to get date and time as part of the file name.

proc format;
picture mydtfmt
low-high = ‘%Y_%0m_%0d_%0I%0M%p’ (datatype=datetime);
run;

PROC EXPORT DATA= Section
OUTFILE= “C:\Documents\My SAS Files(32)\9.3\Exce\section_%unquote(%sysfunc(datetime(),mydtfmt.)).xlsx”
DBMS=EXCEL REPLACE;
SHEET=”section”;
RUN;

Output:

In Windows Explorer,