BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jojo
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

6 REPLIES 6
Ksharp
Super User

select *

from have

   having var=min(var);

Should ba a start .

Keith
Obsidian | Level 7

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;

jojo
Obsidian | Level 7

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.

Reeza
Super User

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.

PGStats
Opal | Level 21

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
jojo
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 21687 views
  • 5 likes
  • 5 in conversation