topic Re: way to refer multiple sqlobs? in New SAS User

way to refer multiple sqlobs?

laiguanyu001 — Mon, 15 Jul 2019 17:05:35 GMT

Hi,

I know that I can use sqlobs to refer to the observations in the proc sql step.

what is I am processing multiple proc sql steps? how do I differentiate the sqlobs from the first step to the sqlobs from the second step? Is there a way to name them?

Re: way to refer multiple sqlobs?

novinosrin — Mon, 15 Jul 2019 17:10:09 GMT

Would copying the value to another macro var help?

%let sql_obs1= &sqlobs;

on the next proc sql

you could incremet

and load %let sql_obs2= &sqlobs;

and so on

Re: way to refer multiple sqlobs?

ballardw — Mon, 15 Jul 2019 17:22:43 GMT

@laiguanyu001 wrote:

Hi,

I know that I can use sqlobs to refer to the observations in the proc sql step.

what is I am processing multiple proc sql steps? how do I differentiate the sqlobs from the first step to the sqlobs from the second step? Is there a way to name them?

If you currently have multiple SELECT clauses in a single Proc SQL step then you would need to modify the code to one Select per Sql call and assign the resulting sqlobs to a separate macro variable. Something like:

proc sql;
   select clause
   from source
   ;
quit;
%let count1 = &sqlobs.;
proc sql;
   select clause2
   from source2
   ;
quit;
%let count2 = &sqlobs.;

Possibly more descriptive names than Count1 and Count2 would be good idea to better identify the source.

Re: way to refer multiple sqlobs?

Tom — Mon, 15 Jul 2019 17:31:44 GMT

Just store the value.

proc sql noprint;
select distinct name into :male_names separated by ' '
from sashelp.class
where sex='M'
;
%let num_males=&sqlobs;
select distinct age  into :male_ages separated by ' ' 
from sashelp.class 
where sex='M'
;
%let num_male_ages=&sqlobs;
quit;