Kreara

An alternative method for pre-load format with PROC SQL

Prepared by Sujith K G (sujith@kreara.com)

Preloadfmt is an option which works with proc means, proc tabulate and proc summary. This option requests SAS to pre-load all the format values for a class variable even if the particular value is not presented in the dataset i.e. it enables count to be presented as 0 for a particular category of the variable which is missing in the dataset.

Proc Sql is a widely used SAS procedure. In Proc Sql neither is the Preloadfmt option available nor is there any other option that supports the preload function. The following example describes a method for obtaining the preloadfmt functionality.

The concept is to take the Cartesian product of the class variables for which the results have to be obtained.

The following dataset presents 15 subjects falling into two groups of subjects with four different treatment groups applied between them,

data eg01;

input id grp $ trt $;

cards;

01 A TRT1

02 B TRT3

03 A TRT4

04 A TRT1

05 B TRT1

06 A TRT3

07 A TRT4

08 B TRT1

09 A TRT1

10 A TRT3

12 B TRT3

13 A TRT1

14 B TRT2

15 A TRT4

;

run;

*Defining formats for treatment and group;

proc format;

value $trtc

"TRT1"="Treatment 1"

"TRT2"="Treatment 2"

"TRT3"="Treatment 3"

"TRT4"="Treatment 4";

value $grpc

"A"="Group A"

"B"="Group B";

run;

*Determining the frequency;

proc freq data=eg01 noprint;

table grp * trt /out=eg02(drop=percent);

run;

*Proc format procedure used for getting dataset with available formats in the session;

proc format cntlout=formats;

run;

The dataset formats contains information of all the formats which is used in this session.

proc sql;

*The Cartesian product;

*Here the formats dataset is separated into two one which contains Group classes and another with treatment classes and the two datasets are joined to form the Cartesian product;

create table fmtcomp as

select a.START label="Group" as grp, b.START label="Treatment" as trt

from formats(where=(fmtname='GRPC')) as a, formats(where=(fmtname='TRTC')) as b;

quit;

The resultant dataset contains all the combinations from the two variables.

proc sql;

*The Cartesian product;

*Here the formats dataset is separated into two one which contains Group classes and another with treatment classes and the two datasets are joined to form the Cartesian product;

create table fmtcomp as

select a.START label="Group" as grp, b.START label="Treatment" as trt

from formats(where=(fmtname='GRPC')) as a, formats(where=(fmtname='TRTC')) as b;

quit;

The final data set is obtained as,