topic Re: Macro Variable Query in SAS Programming

Macro Variable Query

scb — Wed, 08 Aug 2018 06:28:52 GMT

May I know how to pass the "Make" data into the macro variable individually, and subsequently produce the individual "Make" report.

Below example have 4 different car models only, what if I have got 80 - 100 car brands? Anyone can help? Thanks.

data test;
input brand $;
cards;
BMW
Audi
Honda
Toyota
;
run;

proc sql noprint;
select distinct brand
into :brand separated by ''
from Test;
quit;

proc sql;
create table &Brand as select distinct
* from sashelp.cars
where make eq "&Brand";
quit;

Re: Macro Variable Query

Astounding — Wed, 08 Aug 2018 06:48:42 GMT

That's generally a bad idea. Why not create 1 data set holding all 80 brands instead of keeping track of 80 data sets?

Given that this is a training exercise to learn about macro language, the easiest way would be to use CALL EXECUTE:

data _null_;

set test;

call execute('proc sql; create table ' || brand ||

'as select distinct * from sashelp.cars where make eq "' || brand || '"; quit;');

run;

If you are careful about it, you can reduce the code to a single PROC SQL instead of 80 PROC SQLs:

data _null_;

call execute('proc sql;');

do until (done);

set test end=done;

call execute('create table ' || brand ||

'as select distinct * from sashelp.cars where make eq "' || brand || '";');

end;

call execute('quit;');

stop;

run;

Re: Macro Variable Query

PeterClemmensen — Wed, 08 Aug 2018 06:45:00 GMT

You can tweak your code and write a macro that does what you want. However, I much prefer to use the CALL EXECUTE Routine in this type of problem

data test;
input brand $;
cards;
BMW
Audi
Honda
Toyota
;
run;

data callstack;
	set test;
	code=cats("data cars_",brand,"; set sashelp.cars(where=(Make='",brand,"'));run;");
	call execute(code);
run;

Re: Macro Variable Query

RW9 — Wed, 08 Aug 2018 07:40:31 GMT

As @Astounding has said, that is a bad idea. SAS is built around the concept of by group processing. What you, and all these posts whereby lists are put into macro, then loops and multiple dataset, are doing is forcing it away from that core concept.

The problem:

produce the individual "Make" report

Can be re-written:
produce the report by make

Which would look like:

proc report data=sashelp.cars nowd;
  by make;
  title "#byval1";
  columns _all_;
run;

No need for different datasets, macro, or loops. This core concept works in datastep and procedures.