sql conditionally print max of variables

[panda]

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

[novinosrin]

change to having 

having max(v1-v1000) > 1;

[panda]

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. 

[novinosrin]

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

[panda]

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!

[novinosrin]

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;

[PGStats]

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

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

[panda]

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;

[ballardw]

---

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