Month: April 2023

SAS: Proc Tabulate Nested Row Variable and Missing Option

In previous 3 Proc Tabulate related posts, I have focused on table coloring, percentages, and setting order for the class variable .

In the table coloring post, I have provided example of stacking statistics for multiple row variables one after the other. Sometimes, we the variables has a nested relationship, we can use * to identify the hierarch of the variable, eg Faculty*program so the statistics by program are ordered by Faculty and then program. 

proc tabulate data =table (where=(exclude = "NO" )) missing;
class visa fullpart gender program faculty year;
var heads fftes;
table faculty="Faculty" all visa="Visa Status" all fullpart="F/P" all  
gender="Gender" all faculty="Faculty"*program="Program" all,
(year="year" all)*(heads="Heads"*SUM="" heads="% Total"*pctsum fftes="FFTEs"*SUM="" fftes="% Total"*pctsum 
< faculty all visa all fullpart all  gender all faculty*program > =""  );
run;

The above code basically stacking the sum and percentage of heads and FFTEs by year (column) for the row variables in the order of Faculty, Visa, fullpart (full-time or part-time), gender, and Program (nested under Faculty).

Please pay attention to the “missing” option in the Proc tabulate. If there is any missing value in the class variables, the whole observation is removed from the Proc tabulate result, even for the class variable that doesn’t have any missing values, because Proc tabulate force the total statistics for all the class variables to be the same. It is crucial to include the “missing” option, so “” has a value to be included in the Proc Tabulate results.

Here is a good reference document for Proc Tabulate.

Reference: https://www.iuj.ac.jp/faculty/kucc625/sas/tabulate.html

proc tabulate data= sashelp.cars;
class origin type make drivetrain;
var msrp;
table (origin="" all) * (make="" all), (type="" all )*(DriveTrain="" )*(MSRP=""*MEAN N);
run;

Output:
The table produced will show a hierarchical summary:

  • Columns: Each combination of type (e.g., Sedan, SUV) and drivetrain (e.g., 4WD), summarizing msrp with mean and count.
  • Rows: Each combination of origin (e.g., Asia, Europe, USA) and make (e.g., Acura, Audi, BMW), along with totals (all).

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: Create SAS Table from List of Strings

Normally I need to compile a unique list of text strings from SAS table and run report for each item in the list through a %do loop. See details in the previous post. In this case, I need to convert the list of strings back to SAS table using space as delimiter.

Solution

%let lst = apple orange grape carrot banana watermelon peach;
%macro createtbl;
data lst; 
length fruit $15.;
%let i = 1;
%do %while (%scan(&lst., &i., ' ') ne );
  fruit = "%scan(&lst., &i. , ' ')"; 
  output;
%let i = %eval(&i. +1);
%end; 
run;
%mend createtbl;
%createtbl;

Notes

  • Have to use macro with %macro and %mend because %do, %end, and %eval are not valid in open code. Macros with %macro and %mend are closed marcos.
  • Use single quotation ‘ ‘ for space and use double quotation ” ” for string. The other way will not work. In the above example, if I change the equation for fruit to ‘%scan(&lst., &i. , ” “)’, then the string %scan(&lst., &i., ” “) becomes the values in the fruit field without interpreting the macro variable in the equation and applying the scan function.
  • Need %let i = %eval(&i. +1) with the %do % while statement

Wrong Code Demo and Error Diagnosis

data lst; 
length fruit $15.;
do i =1 to 7;
  fruit = "%scan(&lst., i , ' ')"; 
  output;
end;
run;

Error Message

  • ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
  • operand is required. The condition was: i
  • ERROR: Argument 2 to macro function %SCAN is not a number.

Diagnosis

  • The second argument i is not a number. To solve this, have to use closed macro and %eval, so the value of i +1 can be calculated.

Other Methods that don’t work

  • Create empty table first and then add values by row.
  • Don’t know how to add values by row without using macro.
/* NOT USEFUL*/
data lst;
attrib 
    fruit length=$15 format=$15. label="fruit";
stop;
run;