BookmarkSubscribeRSS Feed
Vk_2
Obsidian | Level 7

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

4 REPLIES 4
Reeza
Super User

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

ballardw
Super User

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.

Vk_2
Obsidian | Level 7

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.  

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 690 views
  • 1 like
  • 4 in conversation