As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Expanding variable lists with PROC TRANSPOSE

From sasCommunity
Jump to: navigation, search

Quoting the documentation: A SAS Variable List is an abbreviated method of referring to a list of variable names.

Most procedures and the data step know how to "expand" these lists into the variables they actually refer to.

Sometimes, when writing a reusable program a nice feature would be to allow users to specify list of variables using SAS Variable Lists. However this can pose a problem if you want to process the list of variables individually, as in the program for this question posted to SAS-L Appending RTF files (PROC LOGISTIC) or maybe macro loops?

One way to accomplish this easily is with PROC TRANSPOSE. When PROC TRANSPOSE is called to process a data set with zero observations the output consist simply of the variables from the VAR list transpose to observations. Plus, if any of the variables in the list have variable lables a variable is created to hold the labels. PROC TRANSPOSE names these variables _NAME_ and _LABEL_ by default.

proc transpose data=sashelp.class(obs=0) out=varlist;
   var w: name--age h:;
   run;
proc print;
   run;

To produce the following data set.

Obs    _NAME_

 1     Weight
 2     Name
 3     Sex
 4     Age
 5     Height



Consider a scenario where you have an application that accepts as input a list of variables and within the list secondary information is embedded, information on where page breaks should occur, for example:

%let varlist = age _page_ w: h: _page_ name sex;

Again, PROC TRANSPOSE can be used to expand the variable list and provide information on the location of the string "_PAGE_".

data varlist;
   stop;
   set sashelp.class;
   call missing(_page_);
   run;

This data step creates a zero observation data set using SASHELP.CLASS and adds the variable _PAGE_, otherwise PROC TRANSPOSE will produce "ERROR: Variable _PAGE_ not found."


proc transpose out=varlist;
   var &varlist;
   run;

As before transpose the zero observation data set to produce.

Obs    _NAME_

 1     Age
 2     _page_
 3     Weight
 4     Height
 5     _page_
 6     Name
 7     Sex

Notice that _PAGE_ appears twice in this data set just as it appears in the variable list. This data can be processed in any number of ways to associate a page variable with a variable name. I like to use INFORMATS, to read character values and create numbers. For example the following data step view and PROC FORMAT create a numeric INFORMAT "PGBRK".

data cntlin / view=cntlin;
   retain fmtname 'pgbrk' type 'I' hlo 'UJ ';
   label = 1;
   do until(eof);
      set varlist end=eof;
      start = upcase(_name_);
      if start eq '_PAGE_' then do;
         label + 1;
         continue;
         end;
      output;
      end;
   call missing(start);
   label + 1;
   hlo = cats(hlo,'O');
   output;
   stop;
   run;

proc format cntlin=cntlin;
   select @pgbrk;
   run;
   quit;

The FMTLIB output from PROC FORMAT. Note the SELECT statement used with PROC FORMAT above is used to produce the FMTLIB output, it is not need to create the INFORMAT..

----------------------------------------------------------------------------
|     INFORMAT NAME: @PGBRK   LENGTH:    6   NUMBER OF VALUES:    6        |
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH   6  FUZZ:        0   |
|--------------------------------------------------------------------------|
|START           |END             |INVALUE(VER. V7|V8   22JUL2008:10:12:48)|
|----------------+----------------+----------------------------------------|
|AGE             |AGE             |                                       1|
|HEIGHT          |HEIGHT          |                                       2|
|NAME            |NAME            |                                       3|
|SEX             |SEX             |                                       3|
|WEIGHT          |WEIGHT          |                                       2|
|**OTHER**       |**OTHER**       |                                       4|
----------------------------------------------------------------------------

The INFORMAT can be used later on when the information about which variables should be grouped on a page is needed, in a PROC REPORT perhaps.

Transposing a Zero-Observation Data Set deals with a mirror-image scenario: getting rid of the observations which arise when a zero-observation SAS data set in transposed.