DATA Step, Macro, Functions and more

using proc sql outobs=1 and selecting into a variable how do i do that?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

using proc sql outobs=1 and selecting into a variable how do i do that?

if I have 1 observation from a proc sql query how do i assign that to a variable? that i want to load into a variable 

 

proc sql outobs=1;
SELECT "Qa Test Id"n FROM ROBM.QUAL102;
run

 

so that further on I can do the below sort if the &var (QA Test ID)  from the select above is 35 for instance

 

 

if &var = 35 then do;
 proc sort data=ROBM.QUAL102;
  by TERM_CODE "Id"n CRN;
quit;
end;

Accepted Solutions
Solution
‎04-21-2017 01:28 AM
Super User
Super User
Posts: 7,060

Re: using proc sql outobs=1 and selecting into a variable how do i do that?

[ Edited ]

I think you are looking to create a macro variable.  If so there is no need for the OUTOBS setting since unless you tell it to generate multiple variables or make a delimited list it will only write out one value into the macro variable.

proc sql noprint;
  SELECT "Qa Test Id"n 
    into :mvar
    FROM ROBM.QUAL102
  ;
quit;

You can then test the macro variable, but to conditionally generate code you will need to wrap the code into a macro o ruse some other method to generate the code. IF/THEN statements only work in DATA STEPS or some procs that support data step like data processing code.

%macro xxx ;
%if (&mvar = 35) %then %do;
proc sort data=ROBM.QUAL102;
  by TERM_CODE "Id"n CRN;
run;
%end;
%mend xxx;
%xxx;

 

View solution in original post


All Replies
Super User
Posts: 19,822

Re: using proc sql outobs=1 and selecting into a variable how do i do that?

You need to create a macro variable not a outobs=1 or create a dataset. Your current code will only output the data to the result window.

 

 

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n1y2jszlvs4hugn14...

 

proc sql noprint;
select max(age) into :max_age
from sashelp.class;
quit;

%put &max_age;
Solution
‎04-21-2017 01:28 AM
Super User
Super User
Posts: 7,060

Re: using proc sql outobs=1 and selecting into a variable how do i do that?

[ Edited ]

I think you are looking to create a macro variable.  If so there is no need for the OUTOBS setting since unless you tell it to generate multiple variables or make a delimited list it will only write out one value into the macro variable.

proc sql noprint;
  SELECT "Qa Test Id"n 
    into :mvar
    FROM ROBM.QUAL102
  ;
quit;

You can then test the macro variable, but to conditionally generate code you will need to wrap the code into a macro o ruse some other method to generate the code. IF/THEN statements only work in DATA STEPS or some procs that support data step like data processing code.

%macro xxx ;
%if (&mvar = 35) %then %do;
proc sort data=ROBM.QUAL102;
  by TERM_CODE "Id"n CRN;
run;
%end;
%mend xxx;
%xxx;

 

Frequent Contributor
Posts: 84

Re: using proc sql outobs=1 and selecting into a variable how do i do that?

that works great thanks

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 152 views
  • 0 likes
  • 3 in conversation