DATA Step, Macro, Functions and more

Select Into Problem

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Select Into Problem

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

 

 


Accepted Solutions
Solution
‎09-20-2017 02:03 PM
Trusted Advisor
Posts: 1,231

Re: Select Into Problem

[ Edited ]

Hi,

 

Try this.

 

proc sql;

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

from a

;

Quit;

View solution in original post


All Replies
Solution
‎09-20-2017 02:03 PM
Trusted Advisor
Posts: 1,231

Re: Select Into Problem

[ Edited ]

Hi,

 

Try this.

 

proc sql;

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

from a

;

Quit;

Super User
Posts: 11,343

Re: Select Into Problem

[ Edited ]

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.

Respected Advisor
Posts: 4,936

Re: Select Into Problem

[ Edited ]

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
Occasional Contributor
Posts: 18

Re: Select Into Problem

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.

Occasional Contributor
Posts: 18

Re: Select Into Problem

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,

Super User
Posts: 11,343

Re: Select Into Problem


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 110 views
  • 0 likes
  • 4 in conversation