topic Re: sql conditionally print max of variables in SAS Programming

sql conditionally print max of variables

panda — Wed, 09 May 2018 15:46:04 GMT

Hi,

My data set have variables from v1 to v1000, I'd like to print ids and the max of those variables if the max of them is above 1. I tried sql command but my code does not work:

proc sql;
  create table max as 
  select id, max(v1-v1000)
  from data
  where max(v1-v1000) > 1;
quit;

Any suggestions? Thanks!!

Re: sql conditionally print max of variables

novinosrin — Wed, 09 May 2018 15:53:02 GMT

change to having

having max(v1-v1000) > 1;

Re: sql conditionally print max of variables

panda — Wed, 09 May 2018 15:57:55 GMT

Thanks, the code ran without error but did not generate the results I have in mind. I want to show max of each of column if the max of column is above 1. For instance, if I have 10 variables with max > 1, I'd like to see ten rows of max in the output.

Re: sql conditionally print max of variables

novinosrin — Wed, 09 May 2018 15:59:19 GMT

please provide a sample of what you "have" and what you "want" data? We the community will be able to provide various solutions from which you can choose and pick

Re: sql conditionally print max of variables

PGStats — Wed, 09 May 2018 16:06:33 GMT

Variable lists are not supported in proc SQL. Use a data step instead.

data max;
set data;
sum_v = sum(of v1-v1000);
run;

Re: sql conditionally print max of variables

panda — Wed, 09 May 2018 16:06:46 GMT

The data I have is like this:

data data; 
input id $ v1 v2 v3 v4 v5; 
datalines;
a 1 1 1 1 1
b 2 0 0 1 1
c 1 2 0 0 0
d 1 1 1 1 0
;

I want the output like this:

data want;
input id $ max;
datalines;
b 2
c 2
;

Many thanks!

Re: sql conditionally print max of variables

novinosrin — Wed, 09 May 2018 16:11:28 GMT

data data; 
input id $ v1 v2 v3 v4 v5; 
datalines;
a 1 1 1 1 1
b 2 0 0 1 1
c 1 2 0 0 0
d 1 1 1 1 0
; 

proc sql;
create table want as
select id, max(v1, v2, v3, v4, v5) as max
from data
having max=max( max);
quit;

Re: sql conditionally print max of variables

panda — Wed, 09 May 2018 16:16:12 GMT

Thanks! I think change sum to max and then use proc print can get what I want. It seems that sql cannot select v1-v1000 without typing each of the variable names.

data max;
  set have;
  max = max(of v1-v1440);
  keep id max;
run;

proc print data = max;
 var id max;
 where max > 1;
run;

Re: sql conditionally print max of variables

ballardw — Wed, 09 May 2018 17:27:55 GMT

@panda wrote:

Thanks! I think change sum to max and then use proc print can get what I want. It seems that sql cannot select v1-v1000 without typing each of the variable names.

That is what the previous comment by @PGStatsabout Proc SQL not accepting variable lists means. V1- V1000 is a variable list..