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!!
change to having
having max(v1-v1000) > 1;
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.
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
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!
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;
Variable lists are not supported in proc SQL. Use a data step instead.
data max;
set data;
sum_v = sum(of v1-v1000);
run;
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;
@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..
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.