BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lohia
Calcite | Level 5

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

Hi,

 

Try this.

 

proc sql;

select quote(trim(a)) into :Top3 seperated by ","

from a

;

Quit;

View solution in original post

6 REPLIES 6
stat_sas
Ammonite | Level 13

Hi,

 

Try this.

 

proc sql;

select quote(trim(a)) into :Top3 seperated by ","

from a

;

Quit;

ballardw
Super User

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.

PGStats
Opal | Level 21

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.;

 

PG
Lohia
Calcite | Level 5

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.

Lohia
Calcite | Level 5

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,

ballardw
Super User

@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.

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
  • 6 replies
  • 1119 views
  • 0 likes
  • 4 in conversation