topic Re: Macro design in SAS Programming

Macro design

rlafond — Thu, 27 Dec 2018 19:10:04 GMT

I have a reference table similar to this:

Group	SalesID
Team A	123
Team A	234
Team A	345
Team A	456
Team B	567
Team B	796
Team B	210
Team B	999

I want each iteration of the macro to run for each Group value (or put another way, my final output is per Group), but the lookup value in all of my transaction tables is SalesID. So if I was running a report based on Team B, I would use (in PROC SQL, for example) SalesID in ('567','796','210','999').

A SalesID only ever belongs to one Group, but the number of SalesID per Group varies.

My experience with macros is limited, so when I think about doing this I'm not sure how I could write my code so that each iteration of the macro runs off of multiple values for one parameter (SalesID).

Re: Macro design

Kurt_Bremser — Thu, 27 Dec 2018 19:38:46 GMT

Just join the reference table to the data table and use by processing with group. No macro needed.

Re: Macro design

Astounding — Thu, 27 Dec 2018 23:05:03 GMT

You've given us nothing to go on, to evaluate whether macro language should be used at all. You will need to show a little bit of what happens later for each GROUP.

To start, just subset all the data:

proc sql;

create table subset as select * from main_table

where SalesID in (select SalesID from reference_table);

quit;

That should give you all the observations (for all groups) that you are interested in, since the same SalesID never appears in multiple groups. But the rest of the answer depends on you painting a clearer idea of the goal.

Re: Macro design

rlafond — Fri, 28 Dec 2018 13:02:18 GMT

You're right, I didn't give much detail. I ended up figuring out a solution after playing with it for a bit.