BookmarkSubscribeRSS Feed
panda
Quartz | Level 8

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

8 REPLIES 8
novinosrin
Tourmaline | Level 20

change to having 

 

having max(v1-v1000) > 1;
panda
Quartz | Level 8

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
Tourmaline | Level 20

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
Quartz | Level 8

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
Tourmaline | Level 20
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
Opal | Level 21

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
panda
Quartz | Level 8

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
Super User

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1997 views
  • 0 likes
  • 4 in conversation