I would like your help (or other advice) how to do this.
I have a long list of numeric and character variables to tabulate. Very easy. Very tedious.
I believe I can run a macro, similar to the one below (which doesn't work, by the way), to determine if the variable is NUMERIC or CHARACTER, and then direct it to the appropriate macro.
1) Can you assist with the macro?
2) If not, thoughts on how this obviously common task can be done would be appreciated.
🙂
------------------------------------------------------------
DATA CARS;
SET SASHELP.CARS;
RUN;
%macro TEMP(all_vars=);
%let k=1;
%let var = %scan(&all_vars, &k);
%do %until(&var eq "END");
%if (%datatyp(&var)=NUMERIC ) %then %do;
%TABLECONT(DATA= CARS,BYVAR= ORIGIN,VAR=&var.);
%end;
%else %do;
%TABLECHAR(DATA= CARS,BYVAR= ORIGIN,VAR=&var.);
%end;
%let k = %eval(&k + 1);
%let VAR = %scan(&all_vars, &k);
%end;
%mend temp;
%MACRO TABLECHAR(DATA= CARS,BYVAR= ORIGIN,VAR=);
proc TABULATE DATA=CARS;
class &var ORIGIN ;
TABLE (&VAR ALL),(ORIGIN ALL) *(N="n" COLpctn= "%"*F=5.1);
RUN;
%mend TABLECHAR;
%MACRO TABLECONT (DATA= CARS,BYVAR= ORIGIN,VAR=);
PROC TABULATE DATA=CARS ;
class origin;
var &var;
TABLE (&VAR ),(ORIGIN ALL)
*(N="n" mean='Mean'*F=8.1);
RUN;
%MEND TABLECONT;
%temp(ALL_vars=cylinders type weight msrp END);
: Example with SASHELP.CARS now included.
[If you run it, you will have to manually cancel the submitted statements because DO WHILE is not working]
You seem confused about what macro code does. You use it to generate SAS code. So figure out what SAS code you want to run. Then figure out how it needs to change for numeric or character variables.
Your current code has two major problems.
1) You are using the %DATATYP() to check if the NAME of your variable looks like a number or not. Since numbers are not valid SAS variable names all of the variable names will look like characters to that macro.
2) You are trying to run the CALL EXECUTE() statement outside of a data step.
Let's start with what you have.
I have a long list of numeric and character variables to tabulate.
Your list of variable names does not really provide any useful information for coding without actually know what dataset the variable are in. Variables do not exist independent of datasets.
So first question is what is the dataset you want to analyze?
Also you seem to be wanting to determine what type of analysis based on whether the variable should be treated as categorical or continuous. SAS only has two types of variables: fixed length character strings and floating point numbers. But there is nothing that says that a categorical variable could not be a number. For example month or quarter of a year are numbers, but you probably want to treat them as categories for this type of analysis.
It would be much easier to just tell it which types of analysis you want for each variable.
%macro tables
(dsn= /* Dataset name */
,catvars= /* Space delimited list of categorical variables */
,contvars= /* Space delimited list of continuous variables */
);
%local i var ;
%do i=1 %to %sysfunc(countw(&catvars,%str( )));
%let var=%scan(&catvars,&i,%str( ));
PROC TABULATE DATA=&dsn;
class &var byvar ;
CLASSLEV &var / s=[indent=15 ];
TABLE &var,(BYVAR ALL) *(N="n" COLpctn= "%"*F=5.1) ;
RUN;
%end;
%do i=1 %to %sysfunc(countw(&contvars,%str( )));
%let var=%scan(&contvars,&i,%str( ));
PROC TABULATE DATA=&dsn;
VAR &var ;
CLASSLEV &var / s=[indent=15 ];
TABLE &var,(BYVAR ALL) *(N="n" );
RUN;
%end;
%mend tables;
CALL EXECUTE is a data step routine. Since you only need to call a macro from within macro code, this is not needed.
%if (%datatyp(&var)=NUMERIC ) %then %do;
%TABLECONT(DATA= ,BYVAR= ,CONTVAR=&var.);
%end;
%else %do;
%TABLECHAR(DATA= ,BYVAR= ,CHARVAR=&var.);
%end;
Supply the additional macro parameters as needed.
This simpler program does something like what you are trying to do. Would it be sufficient?
proc freq data=have;
tables (&all_vars) * byvar / norow nopercent;
run;
You seem confused about what macro code does. You use it to generate SAS code. So figure out what SAS code you want to run. Then figure out how it needs to change for numeric or character variables.
Your current code has two major problems.
1) You are using the %DATATYP() to check if the NAME of your variable looks like a number or not. Since numbers are not valid SAS variable names all of the variable names will look like characters to that macro.
2) You are trying to run the CALL EXECUTE() statement outside of a data step.
Let's start with what you have.
I have a long list of numeric and character variables to tabulate.
Your list of variable names does not really provide any useful information for coding without actually know what dataset the variable are in. Variables do not exist independent of datasets.
So first question is what is the dataset you want to analyze?
Also you seem to be wanting to determine what type of analysis based on whether the variable should be treated as categorical or continuous. SAS only has two types of variables: fixed length character strings and floating point numbers. But there is nothing that says that a categorical variable could not be a number. For example month or quarter of a year are numbers, but you probably want to treat them as categories for this type of analysis.
It would be much easier to just tell it which types of analysis you want for each variable.
%macro tables
(dsn= /* Dataset name */
,catvars= /* Space delimited list of categorical variables */
,contvars= /* Space delimited list of continuous variables */
);
%local i var ;
%do i=1 %to %sysfunc(countw(&catvars,%str( )));
%let var=%scan(&catvars,&i,%str( ));
PROC TABULATE DATA=&dsn;
class &var byvar ;
CLASSLEV &var / s=[indent=15 ];
TABLE &var,(BYVAR ALL) *(N="n" COLpctn= "%"*F=5.1) ;
RUN;
%end;
%do i=1 %to %sysfunc(countw(&contvars,%str( )));
%let var=%scan(&contvars,&i,%str( ));
PROC TABULATE DATA=&dsn;
VAR &var ;
CLASSLEV &var / s=[indent=15 ];
TABLE &var,(BYVAR ALL) *(N="n" );
RUN;
%end;
%mend tables;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.