topic Re: What's the most efficient to get the minimum and the maximum value of a variable in SAS Enterprise Guide

What's the most efficient to get the minimum and the maximum value of a variable

alepage — Tue, 07 Sep 2021 14:38:15 GMT

Hello, I am using the following code into a macro function to get the libname, memname, data type and the modate.

proc sql;
create table &nsource. as
select libname,
memname,
memtype,
modate
from sashelp.vtable
where libname="&lbname."
and &enddate.>= datepart(modate)>= &startdate.;
quit;

So I am getting a table gathering this information for each &nsource. Each table could contains about 30 observations.

When those table are created, I could use the libname and the memname as well as a proc sql as below to get the min and max value.

proc sql;

create table &nsource_memname as

select min(transaction_dt) as mintransacdate,

max(transaction_dt) as maxtransacdate,

from &nsource;

quit;

I wonder if there is a more efficient way to do that?

Re: What's the most efficient to get the minimum and the maximum value of a variable

PaigeMiller — Tue, 07 Sep 2021 14:43:46 GMT

Can we see the entire code?

How could this work anyway? There is no variable named transaction_dt in data set &nsource.

Re: What's the most efficient to get the minimum and the maximum value of a variable

Reeza — Tue, 07 Sep 2021 16:06:52 GMT

PROC MEANS/SUMMARY are the most efficent.

Re: What's the most efficient to get the minimum and the maximum value of a variable

ballardw — Tue, 07 Sep 2021 16:46:11 GMT

I might be tempted to try something along the lines of:

proc sql;
   select catx('.',libname,Memname) into :memlist separated by ' '
   from sashelp.vtable
   where libname="&lbname."
      and memtype='DATA'
       and &enddate.>= datepart(modate)>= &startdate.;
quit;

data need;
   set &memlist. indsname=dsn;
   source=dsn;
run;

proc summary data=need nway;
   class source;
   var transaction_dt;
   output out=want (drop=_:) 
      min(transaction_dt)=mintransacdate
      max(transaction_dt)=maxtransacdate
   ;
run;

This creates one output data set with the source data set name as a variable.

Re: What's the most efficient to get the minimum and the maximum value of a variable

alepage — Tue, 07 Sep 2021 18:14:41 GMT

It's awesome. Also, as I am not familiar with the set & memlist option. indsname = dsn; I will add a reference: https://blogs.sas.com/content/iml/2015/08/03/indsname-option.html