Using SAS 9.4
proc means data = volume3 min;
var pre_total_SL_count pre_Total_CL_count postop_total_sl_count_2wks postop_total_cl_count_2wks postop_total_sl_count_6wks postop_total_cl_count_6wks;
class study_id;
run;
I have 6 variables and I used a proc means to get the minimum of each variable. One example has a minimum of 12 12 12 12 11 11 (corresponding to the 6 variable above in the 'var' line).
I used the following code to try and output the minimum of the minimum by a group id
proc means data = volume3 noprint nway min;
var pre_total_SL_count pre_Total_CL_count postop_total_sl_count_2wks postop_total_cl_count_2wks postop_total_sl_count_6wks postop_total_cl_count_6wks;
class study_id;
output out= count_min min = x_min;
run;
However, the code I used only return the minimum of the first variable (12 from the example above and I would need it to return the 11 as that is the smallest. The smallest will not always be in the last position, it could be in any of the 6 locations).
Is it possible to output the minimum across all 6 variables? Thank you
Hi @GS2 Okay the logic is simple, Let us know if you can follow the code below. I have created a sample for you and the solution
/*Sample HAVE*/
data have;
call streaminit(12345);
array v(20);
do _iorc_= 1 to 50;
do _n_=1 to dim(v);
v(_n_)=int(rand('uniform',0,20));
end;
output;
end;
run;
/*Solution*/
data want;
set have;
array t v:;
do over t;
if t=0 then call missing(t);
end;
want=min(of t(*));
set have point=_n_;
run;
/*or*/
/*Solution*/
data want;
set have;
array t v:;
call sortn(of t(*));
do over t;
if t then do;
min=t;
leave;
end;
end;
set have point=_n_;
run;
@novinosrin That keeps all of the information in its own column but is there a way to pull out the smallest number that is not zero?
Ah jeez, are you asking row min or column min? Can you please clarify?
Better if you could a clear sample of what you HAVE and WANT
I have the data classed by an ID. So I have 6 different minimums (1 for each variable) per ID and I want to pull out just the smallest non-zero minimum. Is there a good method to do that? I attached an image of what I have. The code I attached produces only the minimum of the first variable. I want the minimum of the each group as long as it is not zero.
@GS2 wrote:
@novinosrin That keeps all of the information in its own column but is there a way to pull out the smallest number that is not zero?
Please give an example or clarification. The smallest number that is not zero could be -999 which could be smaller than all the positive numbers and is certainly not zero.
The data I have is 0 to 20 so I need the smallest non-zero across the 6 variables.
You could answer directly the question from @novinosrin in which he said:
Ah jeez, are you asking row min or column min? Can you please clarify?
Many of us will not download Microsoft Office files as they can be security risks; so we cannot see the data you posted. The preferred method to post data for a SAS problem is: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
Hi @GS2 Please post a clear sample. Okay let me do that for you.
Do you have something like this?
Var1 | var2 | var3 | var4 | var5 | var6 |
1 | 2 | 3 | 4 | 5 | 6 |
0 | 1 | 2 | 3 | 4 | 5 |
the first row min=1
and 2nd row min=1 i.e here you don't consider 0.
Am i right?
@PaigeMiller Sir agreed, AUTONAME is a beautiful option 🙂
Yes you are correct.
Hi @GS2 Okay the logic is simple, Let us know if you can follow the code below. I have created a sample for you and the solution
/*Sample HAVE*/
data have;
call streaminit(12345);
array v(20);
do _iorc_= 1 to 50;
do _n_=1 to dim(v);
v(_n_)=int(rand('uniform',0,20));
end;
output;
end;
run;
/*Solution*/
data want;
set have;
array t v:;
do over t;
if t=0 then call missing(t);
end;
want=min(of t(*));
set have point=_n_;
run;
/*or*/
/*Solution*/
data want;
set have;
array t v:;
call sortn(of t(*));
do over t;
if t then do;
min=t;
leave;
end;
end;
set have point=_n_;
run;
I assume you included the extra SET statement to re-read the observation so that any zeros that were converted to missing are reset back to zero. Note that for this problem there was no need to include the POINT= option on the second SET statement.
Thank you sir @Tom Oops how silly i was. Cheers for correcting me and can't appreciate enough. I mean it 🙂
Nothing lazy about it. That's why SAS added the autoname= option, as a method to reduce typing.
For this particular question you either 1) post process the data
proc means data=sashelp.stocks noprint nway ; class stock; var open high low close adjclose; output out=work.summary min=; run; data work.mins; set work.summary; minval = min(open, high, low, close, adjclose); run;
2) transpose your data so the values hidden in the variable names are actual values. then summarize the Value without the time information. Your variable names apparently contain at least two pieces of information, whatever SL and SL refer to and a time point: Pre, 2wks and 6wks. Each of those for most modeling and reporting processes would work much better as additional variables. Then your response would be
Proc means data=transposed min;
var count;
class study_id;
run;
Then if you ever wanted to know something about the SL/CL or time frame only use the appropriate class variable.
Or even get all the likely needed summaries at one time:
Proc summary data=transposed;
class study_id SLCL time;
var count;
output out=work.summary min=;
run;
the _type_ variable would indicate the min across all the data, within each of study_id, SLCL, or time, and the combinations of study/SLCL, study/Time, SLCL/time and study/SLCL/Time
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.