topic Re: Macro loop to generate Proc Tabulate by origin in SAS Programming

Macro loop to generate Proc Tabulate by origin

commitsudoku — Wed, 26 Aug 2020 10:05:26 GMT

Hello,

In SASHELP.CARS, I would like to make a PROC TABULATE by Origin. So, the first way is to make 3 PROC TABULATE like the following and replace "Asia" by USA and then Europe :

		PROC TABULATE DATA = data out=tabulate;
		where Origin="Asia";
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

But, instead, I would like to automatize this in a macro loop. Could you please help me why the following code won’t work ? It’s the « where Origin=reg; » part that seems to be the problem. Thank you !

data data; set sashelp.cars;run;

data classes;
input id_reg reg_name $ ; 
cards;
1 Asia 
2 Europe 
3 USA 
run;

%macro comp;
%local i reg;

	%do i=1 %to 3;

		proc sql ;
		select reg_name
		into
		:reg_name 
		from classes
		where id_reg = &i.;
		quit;

		%let reg=reg_name;

		PROC TABULATE DATA = data out=tabulate_&i;
		where Origin=reg;
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

	%end;
%mend comp;
%comp

Re: Macro loop to generate Proc Tabulate by origin

PeterClemmensen — Wed, 26 Aug 2020 10:01:01 GMT

No need for a macro. Simply create a "3-dimensional" table like this

PROC TABULATE DATA = sashelp.cars out=tabulate;
    CLASS Origin Make DriveTrain;
    TABLE Origin, Make, DriveTrain / nocellmerge;
run;

Re: Macro loop to generate Proc Tabulate by origin

commitsudoku — Wed, 26 Aug 2020 10:19:36 GMT

Yes, I understand but I made a simple example. In the real database I work with, I need to make a macro 🙂 this is why I insist on making a macro.

Re: Macro loop to generate Proc Tabulate by origin

PaigeMiller — Wed, 26 Aug 2020 10:46:23 GMT

Working with a database doesn't change the validity of the suggestion from @PeterClemmensen

Or if it does make a difference, please explain why.

Re: Macro loop to generate Proc Tabulate by origin

yabwon — Wed, 26 Aug 2020 11:09:55 GMT

Hi,

first, use an ampersand &:

PROC TABULATE DATA = data out=tabulate_&i; where Origin=® /* <-------- an & */ CLASS Make DriveTrain ; TABLE (Make), (DriveTrain) / nocellmerge ; run;

second: try:

select quote(reg_name)
		into...

since, I believe, you want to have:

where Orgin = "Asia"

rather than:

where Orgin = Asia

after the &reg. resolution

third, this part:

%let reg=reg_name;

is redundant, reg_name is already a macrovariable so you can use it inside the proc tabulate (btw. you don't have the "&" here again)

should be: %let reg = &reg_name; if you want it to work.

All the best

Bart

Re: Macro loop to generate Proc Tabulate by origin

PaigeMiller — Wed, 26 Aug 2020 12:06:07 GMT

I guess I'm going to disagree with @yabwon

This code is easily handled with a BY statement, and no macro or macro variable is needed.

PROC TABULATE DATA = data out=tabulate;
		by Origin;
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

Re: Macro loop to generate Proc Tabulate by origin

yabwon — Wed, 26 Aug 2020 12:25:29 GMT

@PaigeMiller no need for disagree. I didn't tell I like OPs code. I just pointed out places where the root cause of the "where issue" might be. 😉

All the best

Bart

Re: Macro loop to generate Proc Tabulate by origin

RichardDeVen — Wed, 26 Aug 2020 12:29:10 GMT

Reply made to same question as cross posted to stackoverflow...

Use a `BY` statement to independently process like grouped subsets of a data set.
Use a `WHERE` statement to select the subset(s) to process.

Example:

ods html file='output.html' style=plateau;

proc sort data=sashelp.cars out=cars_sorted;
  by origin;
run;

title; footnote;
options nocenter nodate nonumber;

PROC TABULATE DATA=cars_sorted;
  by origin;
  where Origin in ("Asia", "Europe", "USA");

  where also make >= 'P'; * further subset for reduced size of output screen shot;

  CLASS Make DriveTrain ; 
  TABLE (Make), (DriveTrain) / nocellmerge ;
run;

ods html close;

Alternatively, use a `TABLE` statement of form `<page dimension>,<row dimension>,<column dimension>` in lieu of `BY` group processing. Such a form does not need presorted data because the <page dimension> is constructed from `CLASS` variables.

Example:

PROC TABULATE DATA=sashelp.cars; /* original data, not sorted */
  where Origin in ("Asia", "Europe", "USA");
  where also make >= 'P'; * further subset for reduced size of output screen shot;

  CLASS Origin Make DriveTrain ; /* Origin added to CLASS */
  TABLE Origin, (Make), (DriveTrain) / nocellmerge ; /* Origin is page dimension */
run;

Output BY statement

Output PAGE dimensioned table

Re: Macro loop to generate Proc Tabulate by origin

commitsudoku — Wed, 26 Aug 2020 12:57:27 GMT

Sorry, but I don't understand except putting "&". So, here is my code, with your suggestions. But it still does not work:

%macro comp;
%local i reg;

	%do i=1 %to 3;

		proc sql ;
		select reg_name
		into
		:reg_name 
		from classes
		where id_reg = &i.;
		quit;

		%let reg=input(reg_name,$10);

		PROC TABULATE DATA = data out=tabulate_&i;
		where Origin in (&reg);
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

	%end;
%mend comp;
%comp

Re: Macro loop to generate Proc Tabulate by origin

yabwon — Wed, 26 Aug 2020 13:10:10 GMT

%macro comp;
%local i reg;

	%do i=1 %to 3;

		proc sql ;
		select QUOTE(reg_name)
		into
		:reg_name 
		from classes
		where id_reg = &i.;
		quit;

		PROC TABULATE DATA = data out=tabulate_&i;
		where Origin in (&reg_name.);
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

	%end;
%mend comp;
%comp

Re: Macro loop to generate Proc Tabulate by origin

commitsudoku — Wed, 26 Aug 2020 13:26:46 GMT

Thanks to all the bits you gave, I think I have found the solution, without using the BY statemen. So here is the code that worked for me. I thank you all very much for your help!

data data; set sashelp.cars;run;

data classes;
input id_reg reg_name $ ; 
cards;
1 Asia 
2 Europe 
3 USA 
run;

%macro comp;
%local i ;

	%do i=1 %to 3;

		proc sql ;
		select reg_name
		into
		:reg_name 
		from classes
		where id_reg = &i.;
		quit;

		PROC TABULATE DATA = data(where=(Origin="&reg_name")) out=tabulate_&i;
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

	%end;
%mend comp;
%comp

Re: Macro loop to generate Proc Tabulate by origin

ballardw — Wed, 26 Aug 2020 14:57:22 GMT

@commitsudoku wrote:

Thanks to all the bits you gave, I think I have found the solution, without using the BY statemen. So here is the code that worked for me. I thank you all very much for your help!
data data; set sashelp.cars;run;

data classes;
input id_reg reg_name $ ; 
cards;
1 Asia 
2 Europe 
3 USA 
run;

%macro comp;
%local i ;

	%do i=1 %to 3;

		proc sql ;
		select reg_name
		into
		:reg_name 
		from classes
		where id_reg = &i.;
		quit;

		PROC TABULATE DATA = data(where=(Origin="&reg_name")) out=tabulate_&i;
		CLASS Make DriveTrain   ; 
		TABLE (Make), (DriveTrain)   / nocellmerge ; 
		run;

	%end;
%mend comp;
%comp

I still think that @PeterClemmensen's suggestion of using a PAGE expression makes sense. If the purpose of the exercise is to create a data set the information for which page the table is from is in the output data set in the _page_ variable (which doesn't appear in the set if a page dimension is not used).