BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MelissaM
Obsidian | Level 7

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]

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

 

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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
Obsidian | Level 7
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
PROC Star

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
Obsidian | Level 7
Unfortunately not. PROC FREQ treats everything as categorical. I have a mix of continuous and categorical.
Tom
Super User Tom
Super User

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
Obsidian | Level 7
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!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1051 views
  • 0 likes
  • 4 in conversation