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

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

robm — Thu, 20 Apr 2017 21:39:47 GMT

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;

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

Reeza — Thu, 20 Apr 2017 21:45:00 GMT

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#n1y2jszlvs4hugn14nooftfrxhp3.htm

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

%put &max_age;

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

Tom — Thu, 20 Apr 2017 21:48:38 GMT

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;

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

robm — Fri, 21 Apr 2017 05:29:43 GMT

that works great thanks