topic get the minimum value of one variable in SAS Programming

get the minimum value of one variable

jojo — Fri, 14 Dec 2012 06:00:43 GMT

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;

Re: get the minimum value of one variable

Ksharp — Fri, 14 Dec 2012 08:39:58 GMT

select *

from have

having var=min(var);

Should ba a start .

Re: get the minimum value of one variable

Keith — Fri, 14 Dec 2012 08:44:27 GMT

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;

Re: get the minimum value of one variable

jojo — Fri, 14 Dec 2012 17:18:10 GMT

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.

Re: get the minimum value of one variable

Reeza — Fri, 14 Dec 2012 17:27:46 GMT

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.

Re: get the minimum value of one variable

PGStats — Fri, 14 Dec 2012 17:41:35 GMT

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;

Re: get the minimum value of one variable

jojo — Fri, 14 Dec 2012 19:52:59 GMT

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.