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

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

Vk_2 — Tue, 17 Jul 2018 19:22:52 GMT

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

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

Reeza — Tue, 17 Jul 2018 19:28:21 GMT

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

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

ballardw — Tue, 17 Jul 2018 20:06:09 GMT

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.

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

Vk_2 — Tue, 17 Jul 2018 20:43:38 GMT

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.

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

SASKiwi — Tue, 17 Jul 2018 21:24:25 GMT

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.