I need to extract the observation with the minimum value of one variable ( with all other variables for this observation) from over several hundred tables created in a macro program. I can combine these tables and get the observation with the minimum value of the variable, is there any efficient way to do this? Thanks!
%macro runit;
%do i =1 %to 15;
%do j = 1 %to 15-&i;
%D(&i,&i, &j, &j);run; *** will create one table;
%end;
%end;
You could try using a dataset list in a datastep view. Assuming that your datasets names all have the same prefix (untested) :
data allScan / view=allScan;
retain minValue 99999;
set myPREFIX: indsname=dsname;
if myVar < minValue then do;
minValue = myVar;
output;
end;
run;
data minObs;
set allScan end=last;
if last then output;
run;
PG
select *
from have
having var=min(var);
Should ba a start .
PROC SUMMARY is good for this type of problem. Here's an example that returns the record with the lowest age in SASHELP.CLASS. Note that if there is more than 1 record with the same minimum value then this procedure will only return the first one (i.e. it always returns only 1 record). If you need to return all records with the minimum value then I would suggest PROC SQL, although this method is likely to be slower than PROC SUMMARY (provided there's enough memory for SUMMARY to work!)
proc summary data=sashelp.class nway;
output out=want1 (drop=_:) minid(age(_all_))=;
run;
proc sql;
create table want2 as
select * from sashelp.class
having age=min(age);
quit;
Thank you for your help. I know this is for exacting the mini from one table, what I need is to get the mini over several hundred tables.
Get the minimum from each table (and the corresponding fields) and then identify the minimum from that list, that will be the overall minimum.
There's a bunch of ways to do that, but it depends on how your tables are named. If you're creating it in a loop, your best off finding the minimum each loop and appending that to a table to check later on.
What happens if you have multiple minimums, ie observations with the same value that is the minimum.
You could try using a dataset list in a datastep view. Assuming that your datasets names all have the same prefix (untested) :
data allScan / view=allScan;
retain minValue 99999;
set myPREFIX: indsname=dsname;
if myVar < minValue then do;
minValue = myVar;
output;
end;
run;
data minObs;
set allScan end=last;
if last then output;
run;
PG
Thanks! I need to get all records with the mini value, so I created a dataset view and used proc sql to get those records. Thanks very much for your help.
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.