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
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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.