DATA Step, Macro, Functions and more

execute each value of macro variable once for the set of steps and append to a dataset

Reply
Occasional Contributor
Posts: 7

execute each value of macro variable once for the set of steps and append to a dataset

I am trying to execute each value of my macro variable once for the set of steps(bunch of proc sql and data steps) and append the results to a sas data set.

Suppose I have the code like this

*macro variable

%let Customer_ID=(‘N10045’)

 

Proc sql;

….

Where customer_id =&Customer_ID

.....

Proc sql;

…..

Data;

…..

 

Final data set  has three columns Customer_ID,MONTH, Value

 

This works fine but now what I want is suppose to find the top 10 customer_id which is coming from table table1

Select top 10 customer_id from table1;

So what I am looking for is, it has to consider the first customer_id and form a data set Customer_ID,MONTH, Value . So the next customer_id should be appended to the dataset.

Which might look like this table

 

customer_id

MONTH

value

N1205

jan

678

N1205

feb

2869

N1205

march

2676

N1205

august

278

N17890

jan

6898

N17890

march

2178

N17890

Jun

2188

 

My steps does not want all to be bought at the same time for execution as it messes up the data as I have to created bunch of macros. How do I bring in each value of customer_id from table1 and find the final_data_set and append to final_data_set?

sas EG version 7.12

Super User
Posts: 23,951

Re: execute each value of macro variable once for the set of steps and append to a dataset

Add the PROC APPEND step to the end, but look into CALL EXECUTE to call it rather than have a macro variable list. 

It may also be easier to do BY group processing depending on what you're doing, but we can't tell that from what you've posted.

 

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

 

A more complex example is here:

https://gist.github.com/statgeek/b308ac2cfc9b4db0ee3d793567627af0

Super User
Posts: 13,889

Re: execute each value of macro variable once for the set of steps and append to a dataset

Define what "top 10" means.

You should consider having an actual date value instead of text month so that anything that ever wants order by time actually has a chance of working. Graphs are a good example unless you really like having April next to August at the start of a graph...

With an actual date value you could have multiple years worth of data in a  single data set and select records by time as needed.

Occasional Contributor
Posts: 7

Re: execute each value of macro variable once for the set of steps and append to a dataset

Top 10 is for a specific year -Select top 10 customer_id from table1 where year=’2016’

That was just an example to be precise my months have number value which is months of exposure. I have continuous values for months for each customer_id. The reason why I am not bringing all the customer_id ‘s at once is I have product codes which they buy commonly for each customer which I am saving as a macro variable. Which is the reason I wanted to consider one customer id at a time. This may not be the most efficient.  

Super User
Posts: 4,013

Re: execute each value of macro variable once for the set of steps and append to a dataset

You are making it hard for yourself by structuring your data with separate MONTH and YEAR variables. You will find it is much easier if you create a SAS DATE variable which combines your MONTH and YEAR variables.

Ask a Question
Discussion stats
  • 4 replies
  • 70 views
  • 1 like
  • 4 in conversation