DATA Step, Macro, Functions and more

sql conditionally print max of variables

Reply
Contributor
Posts: 35

sql conditionally print max of variables

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!!

PROC Star
Posts: 1,312

Re: sql conditionally print max of variables

change to having 

 

having max(v1-v1000) > 1;
Contributor
Posts: 35

Re: sql conditionally print max of variables

Posted in reply to novinosrin

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. 

PROC Star
Posts: 1,312

Re: sql conditionally print max of variables

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

Contributor
Posts: 35

Re: sql conditionally print max of variables

Posted in reply to novinosrin

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!

 

PROC Star
Posts: 1,312

Re: sql conditionally print max of variables

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;
Esteemed Advisor
Posts: 5,398

Re: sql conditionally print max of variables

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

 

data max;
set data;
sum_v = sum(of v1-v1000);
run;
PG
Contributor
Posts: 35

Re: sql conditionally print max of variables

[ Edited ]

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;
Super User
Posts: 13,028

Re: sql conditionally print max of variables


@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..

Ask a Question
Discussion stats
  • 8 replies
  • 95 views
  • 0 likes
  • 4 in conversation