Using PROC EXECUTE to call PROC TABULATE (please advise)

[MelissaM]

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]

[Kurt_Bremser]

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.

[MelissaM]

Kurt, Thank you so much.
After oh, another ? hrs, I still need your help.
For this round, I not only fixed my original message, but also used SASHELP.class so there's actual data.
The program will have to be manually stopped, unfortunately. But don't let that scare you.
I've actually been at this for a couple weeks, so I really appreciate the guidance.

[Astounding]

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;

[MelissaM]

Unfortunately not. PROC FREQ treats everything as categorical. I have a mix of continuous and categorical.

[Tom · Accepted solution]

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;

[MelissaM]

Tom,
What I lack in macro skills might be analogous to what others lack in, say, social skills.
I remind you that we are all learning here. I ask that you be mindful of your tone.
That said; your interpretation of what I was doing incorrectly was spot on correct.
And more than that, I appreciate the amazing macro!