topic Re: Query to find min and max of multiple datasets from same library in SAS Programming

Query to find min and max of multiple datasets from same library

Babloo — Tue, 07 May 2019 16:27:33 GMT

I have multiple datasets with different names under the the same library. All the variables in those datasets are same. Now I want to query the Minimum And maximum value of particular variable of all those datasets in a step or two.

Appreciate if someone of you guide me here.

Re: Query to find min and max of multiple datasets from same library

Reeza — Tue, 07 May 2019 16:43:57 GMT

Assuming the data sets are the same structure and can be stacked, you can create a view and summarize that.Or you can summarize each data set individually and extract that information from the summary data.

data have / view=have;
set data1-data10;
run;

proc means data=have min max;
var var1-var3;
run;

@Babloo wrote:

I have multiple datasets with different names under the the same library. All the variables in those datasets are same. Now I want to query the Minimum And maximum value of particular variable of all those datasets in a step or two.

Appreciate if someone of you guide me here.

Re: Query to find min and max of multiple datasets from same library

Babloo — Tue, 07 May 2019 16:48:02 GMT

Datasets names are like employee, salary, address etc and it is not like
data1 to data2.

I failed to mention this in my initial post.

Re: Query to find min and max of multiple datasets from same library

Reeza — Tue, 07 May 2019 16:49:08 GMT

Please post sample data and expected output.

Re: Query to find min and max of multiple datasets from same library

Reeza — Tue, 07 May 2019 16:50:09 GMT

@Babloo wrote:
Datasets names are like employee, salary, address etc and it is not like
data1 to data2.

I failed to mention this in my initial post.

You said:

>All the variables in those datasets are same

If that true regardless of names? If so that's fine, just list the names. If not, then you have a much more complex problem.

Re: Query to find min and max of multiple datasets from same library

PaigeMiller — Tue, 07 May 2019 16:51:08 GMT

Datasets names are like employee, salary, address etc and it is not like data1 to data2.

You replace the data1-data10 in the example code with the names of your actual data sets.

Re: Query to find min and max of multiple datasets from same library

andreas_lds — Tue, 07 May 2019 16:57:10 GMT

Untested code:

proc sql noprint;
  select MemName
    into :datasetList separated by ' '
    from sashelp.vtable 
      where LibName = 'LIBNAME_ALL_UPCASE'
  ;
quit;

data have / view=have;
  set &datasetList.;
run;

Then use proc means to calculate the statistics.

Re: Query to find min and max of multiple datasets from same library

ballardw — Tue, 07 May 2019 17:02:44 GMT

@Babloo wrote:

I have multiple datasets with different names under the the same library. All the variables in those datasets are same. Now I want to query the Minimum And maximum value of particular variable of all those datasets in a step or two.

Appreciate if someone of you guide me here.

Another point to clarify, do you want the single minimum/ maximum across all of the data sets or for each dataset with an indicator of which set each max/ min is from?

Re: Query to find min and max of multiple datasets from same library

svh — Tue, 07 May 2019 17:06:54 GMT

Very interesting to see different approaches. I realize mine is probably less efficient than the others. I really like to use macros with CALL EXECUTE, so my strategy was to use dictionary tables (like another user did) to obtain the list of tables that need to be summarized. With this, I created a table of data set names that could be used with a CALL EXECUTE statement. This way, the macro with PROC MEANS can run on each data set. At the end, a DATA step concatenates all of the summary tables.

IMPORTANT: This code assumes that your TEST library ONLY has the tables you want to use to calculate summary statistics.

libname test '<PATH>';

data test.A;
  input x y;
  cards;
  1 2
  3 4
  5 6
;
run;

data test.b;
  input x y;
  cards;
  7 8
  9 10
  11 12
;
run;

proc sql;
   create table macros as
   select strip(memname) as table_name from dictionary.tables
   where libname = 'TEST';
quit;

%macro means(dsn);
proc means data=Test.&dsn;
   var x;
   output max=maximum min=minimum out=Test.Summary&DSN ;
run;
data Test.Summary&DSN;
   set Test.Summary&DSN;
   dataset = "&dsn";
%mend means;  

data _null_;
set macros;
  call execute('%means('||table_name||')');
  run;

data Test.Finished;
  set Test.Summary:;
  run;