topic Finding the latest of a series of data sets in SAS Programming

Finding the latest of a series of data sets

LHV — Tue, 24 Apr 2012 22:45:29 GMT

Hi,

There is a data set in a library which is produced monthly in general, say e.g. SOURCE.SALES_YYYYMM

Here is SOURCE is a library nam, SALES_YYYYMM is data set name and YYYYMM is 201201 for the month of Jan2012.

But some months, the data set is not produced.

However, whenever i run a particular piece of code that needs to use the latest of those series of data set.

e.g.

..................

data result;

set SOURCE.SALES_201204(keep=var1 var2 var3);

run;

..................

At the moment before running the code i manually check and replace the data set with the latest one in the code.

I want to avoid that manual intervention and automate that process, and wrote the following code.

%macro choose_latest;

%let YYYYMM =%sysfunc(putn( %sysfunc(today()),yymmn6.));

%let data_set = SOURCE.SALES_&YYYYMM;

%if %sysfunc(exist(&data_set.)) %then;

%else %do;

i =1;

%do %while( i ne 0);

call symput( 'YYYYMM' , %sysfunc(putn( %sysfunc(intnx(month,%sysfunc(today()),-i,b)),yymmn6.)));

call symput('data_set', "SOURCE.SALES_&YYYYMM");

%if %sysfunc(exist(&data_set.)) %then i=0;

%else i=i+1;

%end;

%mend;

%choose_latest;

However, when i submit the code the session just freezes and doesn't respond.

Would any one help me to fix the problem?.

Many thanks in advance.

Re: Finding the latest of a series of data sets

data_null__ — Tue, 24 Apr 2012 22:50:15 GMT

Why not just use DICTIONARY.MEMBERS and take SALES_yyyymm with the largest value INTO :data_set

56 data SOURCE.SALES_201203 SOURCE.SALES_201204 SOURCE.SALES_201201;

57 stop;

58 run;

NOTE: The data set SOURCE.SALES_201203 has 0 observations and 0 variables.

NOTE: The data set SOURCE.SALES_201204 has 0 observations and 0 variables.

NOTE: The data set SOURCE.SALES_201201 has 0 observations and 0 variables.

NOTE: DATA statement used (Total process time):

real time 0.06 seconds

cpu time 0.01 seconds

59 %let data_set=;

60 proc sql noprint;

61 select max(memname) into :data_set

62 from dictionary.members

63 where libname eq 'SOURCE';

64 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

65 run;

66 %put NOTE: DATA_SET=&data_set;

NOTE: DATA_SET=SALES_201204

Message was edited by: data _null_

Re: Finding the latest of a series of data sets

Patrick — Tue, 24 Apr 2012 23:16:17 GMT

As a small extension to "data _null_"s code I probabely would add the memname to the where clause in case there are also other tables in library SOURCE.

where libname eq 'SOURCE' and memname like 'SALES%'

Re: Finding the latest of a series of data sets

LHV — Tue, 24 Apr 2012 23:23:57 GMT

Hi,

Thank you for your very prompt reply. In my SOURCE library there are thousands of datasets with different name series.

However, i think, the following code will give my latest sales_YYYYMM dataset.

%let data_set=;

proc sql noprint;

select max(memname) into :data_set

from dictionary.members

where libname eq 'SOURCE' and memname like 'SALES%';

quit;

If not, please correct me. I don't have access to sas just now and will try it later.

By the way, do you find any error in my code?

Many thanks again,

Re: Finding the latest of a series of data sets

data_null__ — Tue, 24 Apr 2012 23:38:35 GMT

Yes that should work or even

memname eqt 'SALES_'

Re: Finding the latest of a series of data sets

Patrick — Wed, 25 Apr 2012 02:01:57 GMT

EQT for dealing with an underscores instead of using a LIKE with the need to define an escape character - nice! ....and thinking a bit longer I believe to remember that you've taught me this one already a couple of months ago. Hmm... getting old!

Re: Finding the latest of a series of data sets

Ksharp — Wed, 25 Apr 2012 04:51:23 GMT

I think you need another clause ORDER BY to the closest dataset name.

proc sql noprint;
     select memname into : data_set
     from dictionary.members
     where libname eq 'WORK' and memname eqt 'SALES_'
     order by input(compress(memname, ,'kd'),best8.) desc;
quit;

%put &data_set ;

Ksharp

Re: Finding the latest of a series of data sets

Patrick — Wed, 25 Apr 2012 04:59:38 GMT

Why? It's SELECT MAX(memname)

Re: Finding the latest of a series of data sets

Ksharp — Wed, 25 Apr 2012 05:41:20 GMT

Never mind, It is my personal favor.