Hi guys,
I was working with select into clause, faced an interesting problem. Need to pull top 3 market values and want to store into a macro variable. so that i can pass this to if condition .
If Mrkt in(&Top3) then output ;
A
P001
P002
P003
P004
P005
P006
Proc Sql(outobs=3);
select a into :Top3 seperated by ","
from a
;
Quit;
%put &Top3 ;
P001,P002,P003
But i need top3 macro variable values like 'P001','P002','P003' ;
Thanks
Hi,
Try this.
proc sql;
select quote(trim(a)) into :Top3 seperated by ","
from a
;
Quit;
Hi,
Try this.
proc sql;
select quote(trim(a)) into :Top3 seperated by ","
from a
;
Quit;
Describe the rule for identifying "top 3" if they are not the first 3 records of the set.
And do you need the values quoted? if so
Proc Sql outobs=3 noprint; select quote(trim(a)) into :Top3 separated by "," from a ; Quit; %put &Top3 ;
You may need the trim(a) to prevent getting values like "P001 ","P002 " and such. The noprint suppresses sending output to the results window/destination.
If you need the top values, you somehow need the value in your data. So the solution could look like this:
data have;
input mrkt $ value;
datalines;
P001 2
P002 1
P003 5
P004 7
P005 9
P006 0
;
proc sql outobs=3 noprint;
select quote(trim(mrkt))
into :top3 separated by ","
from have
order by value desc;
quit;
%put &top3.;
Hi Guys,
Thank you for your help, solution worked perfectly and top values solution is already handled, just need to capture these values to use in other part of the code.
Hi guys, there is one more thing i wanna discuss.
Problem:
Code run to produce a summarized report for top 5 states on top 5 disease.
Currently i'm running 6 codes to produce these stats. One for, to find out the top 5 and then run on them and get whole summary(Using different values and parameter)
Then other 5 codes to generate seperate reports on 5 states(because they have different data/parameter values)
Now i wanna combine these to one, after running for whole i wanna store top 5 states values into a variable and wanna pass it to macro progamm to assign
state value one by one and prodce results on different parameter values.
Any suggestion around this problem?
Thanks,
@Lohia wrote:
Hi guys, there is one more thing i wanna discuss.
Problem:
Code run to produce a summarized report for top 5 states on top 5 disease.Currently i'm running 6 codes to produce these stats. One for, to find out the top 5 and then run on them and get whole summary(Using different values and parameter)
Then other 5 codes to generate seperate reports on 5 states(because they have different data/parameter values)
Now i wanna combine these to one, after running for whole i wanna store top 5 states values into a variable and wanna pass it to macro progamm to assign
state value one by one and prodce results on different parameter values.Any suggestion around this problem?
Thanks,
Suggestions:
1) Start a new thread. This question is much more complicated than this thread and deserves a separate thread
2) Post some example of the data you have, the code you are using and what the result looks like
Post data in the form of data step so we don't have to ask what types of values, variable type and such, and the example code all in code boxes using the forum {i} menu icon.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
3) Likely approaches will involve having a data set that has some of the information related to how to process each state when needed such as dataset names, variables for which role. So you may think about setting that data set up as well if it does not already exist.
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.