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..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.