Tag: format

SAS: Proc SQL Format

  • get variable with the required format from other table with sql join and format in one statement.
  • the example shows the new table contains the new character variable newvar1 with $1. format and the new date variable newvar2 with ddmmyy10. format
 proc sql;
create table new as
select a.* , b.var1 as newvar1 format=$1. , datepart(b.var2) as newvar2 format=ddmmyy10. 
from old1 as a 
left join lib.old2 as b
on a.var = b.var;
quit;

SAS: Convert format catalogs from 32bit to 64bit

Reference: http://support.sas.com/kb/44/047.html

Error message:
ERROR: File FORMATS.CATALOG was created for a different operating system.
Step 1: In windows 32-bit SAS, create a transport file (.cpt) with PROC CPORT and file option.

libname my32 'X:\Work\SAS\formats'; /* path where commonfmt.sas7bcat exists */
filename cat1 'X:\Work\SAS\formats\commonfmt.cpt';  /* transport file you are creating */

proc cport lib=my32 file=cat1 memtype=catalog;
   select commonfmt;
run;

The .cpt file will contain the format information of commonfmt.sas7bcat catalog file.

Step 2: In windows 64-bit SAS, unload the transport file (.cpt) using PROC CIMPORT and infile option.

libname my64 'X:\Work\SAS\format64';  /* path to store the new Formats.sas7bcat file */
filename trans1 'X:\Work\SAS\formats\commonfmt.cpt';  /* same as in Step 1 above */

proc cimport infile=trans1 lib=my64;
run;

Step 3: Check the formats in windows 64-bit SAS.

libname sasfmt  'X:\Work\SAS\format64';

PROC CATALOG CATALOG = SASFMT.COMMONFMT;
CONTENTS;
QUIT;

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;