topic Re: how to avoid warning message when creating macro variables in proc sql in SAS Programming

how to avoid warning message when creating macro variables in proc sql

knveraraju91 — Thu, 30 Jan 2020 19:23:48 GMT

Dear

I am getting a warning message when the the code. I am getting the output i need . I need to have param1 =SS-S1 SSS >= 1 1 and param2=SS-S1 SSS < 1. Please suggest.

WARNING: The query as specified involves ordering by an item that doesn't appear in its SELECT clause. Since you are ordering
the output of a SELECT DISTINCT it may appear that some duplicates have not been eliminated.

Thank you

data one;
input a $1-14 b 16;
datalines;
SS-S1 SSS >= 1 1
SS-S1 SSS < 1  2
;

proc sql;
select strip(put(count (distinct a), best.)) into: cntt from one;
select  distinct a into :param1 -:param&cntt. from one
order by b ;
quit;
	%put &param1;
%put &param2;

Re: how to avoid warning message when creating macro variables in proc sql

novinosrin — Thu, 30 Jan 2020 19:52:19 GMT


/*This is all you need*/
proc sql ;
select count(distinct a) into: cntt trimmed from one;
select   a into :param1 -:param&cntt. 
from (select distinct b,a from one);
quit;

%put &param1;
%put &param2;
/*Or just even terse*/
proc sql ;
select   a into :param1 - 
from (select distinct b,a from one);
quit;

%put &param1;
%put &param2;

Re: how to avoid warning message when creating macro variables in proc sql

Tom — Thu, 30 Jan 2020 21:51:47 GMT

You have two issues here. The one the message is about is that you are trying to select distinct values of A but order the result be a different column. If the same value of A has multiple values of B then your query will return multiple values of A.

So if the same value of A has many values of B which of those values do you want to use for ordering? Perhaps the minimum value?

So instead of using DISTINCT do a subquery using GROUP BY to get to one observation per value of A.

proc sql noprint;
select  a into :param1- 
from (select a,min(b) as b from one group by a)
order by b
;
%let cntt = &sqlobs;
quit;

Now you will see a different message:

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

You could add the B variable to the select list. But then you get a WARNING.

WARNING: INTO clause specifies fewer host variables than columns listed in the SELECT clause.

So you need to stuff B into something. For example you could just have it create a macro variable named DUMMY and then ignore that macro variable.

proc sql noprint;
select a,b into :param1- ,:dummy
from (select min(b) as b,a from one group by a)
order by b
;
%let cntt = &sqlobs;
quit;

Of course it looks like you can skip all of this nonsense and just generate the macro variables use a data step instead of PROC SQL. Do your values of B really count sequentially like in your example? Why not use the value of B to generate the macro variable name.

data _null_;
  set one end=eof;
  call symputx(cats('param',b),a);
  if eof then call symputx('cntt',b);
run;

If not then use the automatic loop counter variable, _N_, instead of B .