Solved
Contributor
Posts: 50

# get the minimum value of one variable

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;

Accepted Solutions
Solution
‎12-14-2012 12:41 PM
Posts: 5,535

## Re: get the minimum value of one variable

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

PG

All Replies
Super User
Posts: 10,784

## Re: get the minimum value of one variable

select *

from have

having var=min(var);

Should ba a start .

Regular Contributor
Posts: 151

## Re: get the minimum value of one variable

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;

Contributor
Posts: 50

## Re: get the minimum value of one variable

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.

Super User
Posts: 23,763

## Re: get the minimum value of one variable

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.

Solution
‎12-14-2012 12:41 PM
Posts: 5,535

## Re: get the minimum value of one variable

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

PG
Contributor
Posts: 50

## Re: get the minimum value of one variable

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.

🔒 This topic is solved and locked.