Tag: Proc format

SAS: Delete and Copy Specific format from Catalog

Delete $year format from sasfmt.commonfmt catelog.

  • Use year.formatc to indicate character format.
  • Use .format to indicate numeric format.
  • “/ entrytype = format” can be omitted.
  • For Proc Catalog procedure, need Quit to close it.
  • Also no need to put $ sign before the format name Year. 
proc catalog catalog =sasfmt.commonfmt ;
delete year.formatc / entrytype=format;
quit;

Assume there is a $year format in the work library. Copy the $year format to the sasfmt.commonfmt.

The best practice is developing a local permanent format catalog for common usage, in this case the sasfmt.commonfmt. If I need to create new format for the SAS work and the format is reusable, then I will copy the format from work library to the sasfmt.commonfmt catalog so I don’t have to recreate the format in the future.

  • Use Proc Catalog procedure
  • catalog= for the source catalog
  • out= for the target catalog
  • Use Copy statement for the copying formats
  • Use Select statement to select specific format to be copied. If Select statement is omitted, then all the formats in the source catalog will be copied to the target catalog.
Proc catalog catalog=work.formats;
copy out=SASFMT.COMMONFMT;
select year.formatc;
quit;

Check the $Year format in the sasfmt.commonfmt catalog.

  • Use Proc Format procedure
  • Use fmtlib option to display the details of format.
  • lib= for the format catalog
  • Use Select statement to identify specific formats to be displayed. This becomes useful when the catalog contains a large number of formats. 
proc format fmtlib lib=sasfmt.commonfmt ;
select $year; run;

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 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,

SAS1-1