topic Re: Macro with proc sql in SAS Programming

Macro with proc sql

jc76 — Mon, 24 Aug 2020 13:20:43 GMT

Hello,

I'm trying to write a simple do loop where it'll iterate over a table a few times and create a new table. My logic is as follows

 

%MACRO conditions_1;

where contact = "IC"

%MEND conditions_1;

%MACRO conditions_2;

where contact = "IC"

and RTW_status = "RTW"

%MEND conditions_2;

proc sql;

create table IB_1 as

(select

invt_mth
, Inventory
, product
, age
,New_program
,count(contact) as contact_IC
,sum(exposure) as totalsum

from vv.maintenance
%conditions_1
group by 1,2,3,4,5

)

union all

(select
invt_mth
, Inventory
, age
, 'Overall' as product
, New_program
,count(contact) as contact_IC_Metric_1
,sum(exposure) as totalsum_Metric_1

from vv.maintenance
%conditions_1
group by 1,2,3,5
)
order by 1,2,3,4,5;
;
quit;

I want my code to run from conditions 1 to 2, and each time, create a new table from IB_1 to 2

Thank you in advance for your help

Re: Macro with proc sql

ballardw — Mon, 24 Aug 2020 15:00:09 GMT

Show the code that works without any macro or variables that does what you envision in "code to run from conditions 1 to 2". Because I cannot figure out what you mean by "from conditions 1 to 2" .

Re: Macro with proc sql

jc76 — Mon, 24 Aug 2020 15:12:25 GMT

proc sql;
create table IB_1 as
(select

invt_mth
, Inventory
, product
, age
,New_program
,count(contact) as contact_IC
,sum(exposure) as totalsum

from vv.maintenance
where contact = "IC"
group by 1,2,3,4,5

)

union all

(select
invt_mth
, Inventory
, age
, 'Overall' as product
, New_program
,count(contact) as contact_IC_Metric_1
,sum(exposure) as totalsum_Metric_1

from vv.maintenance
where contact = "IC"
group by 1,2,3,5
)
order by 1,2,3,4,5;
;
quit;

Hi Ballardw, Please find the code above without the macro. I would like to run my proc sql with different parameters. I want run once with the parameters 1 and a second time with the parameters 2 like a loop. I've got 100 parameters I don't write to 100 times the code

parameters 1: where contact = "IC"

parameters 2: where contact = "IC"
and RTW_status = "RTW"

Thank you for your help

Re: Macro with proc sql

Reeza — Mon, 24 Aug 2020 15:21:52 GMT

Look at CALL EXECUTE.

Additionally, here are some other macro resources. The second one shows you how to convert a working program to a macro. Make your conditions your parameter and pass that to the macro each time. You may need some macro masking if you have complex filters but if your filters are in a data set this is a simple quick way to make it loop.

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Re: Macro with proc sql

ballardw — Mon, 24 Aug 2020 17:19:15 GMT

Lets start with a more basic idea:

Provide a small example data set and what you want for a result from that example data.

The example you are showing looks to me like a very complicated way to do some of what Proc Summary does to get summary results of different groups of variables but without data I can't test your code to see what the results actually are.

Consider this example you should be able to run as the SASHELP.CLASS data set should have been provided in your install:

proc summary data=sashelp.class;
   class sex age;
   var weight height;
   output out=work.classsummary 
       n(weight)=  weight_count
       sum(height)  =  height_sum
   ;
run;

The output data set will contain requested summaries for the VAR variables grouped by 1) all records (_type_=0), Age only (_type_=1) , Sex only((_type_=2) and Sex and Age grouped together (_type_=3).

If you have other variables they might go on the Class statement or could be used in a BY statement to create summaries for each level. Note that the _type_ variable is provided so you could select the combination types you want in the final output. Or the procedure can use an auxiliary data set with the CLASSDATA= option that provides the explicit combinations of the class variables you want in your output.

The Proc Summary /Mean may be much easier that looping through 100's of values and likely to execute much quicker as the procedure likely only needs to read the input data one time.

I have some processes that use this approach to create over 60 _type_ variables one time and then I can select which summary I want to display using Proc Print/Report.

Think combinations of Region, county, school district, school, grade, school type as the base groups but provide summaries by Region and grade, school type; Region and county, grade school type; Region and school district etc... lots of different summaries. I can write reports by selecting the desired result value for Region and "_type_".