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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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