BookmarkSubscribeRSS Feed
rlafond
Obsidian | Level 7

I have a reference table similar to this:

 

GroupSalesID
Team A123
Team A234
Team A345
Team A456
Team B567
Team B796
Team B210
Team B999

 

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).

3 REPLIES 3
Astounding
PROC Star

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.

rlafond
Obsidian | Level 7

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 544 views
  • 1 like
  • 3 in conversation