DATA Step, Macro, Functions and more

Finding the latest of a series of data sets

Reply
Occasional Contributor LHV
Occasional Contributor
Posts: 15

Finding the latest of a series of data sets

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;

                      %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.

Respected Advisor
Posts: 3,799

Re: Finding the latest of a series of data sets

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_

Respected Advisor
Posts: 4,173

Re: Finding the latest of a series of data sets

Posted in reply to data_null__

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%'

Occasional Contributor LHV
Occasional Contributor
Posts: 15

Re: Finding the latest of a series of data sets

Posted in reply to data_null__

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,

Respected Advisor
Posts: 3,799

Re: Finding the latest of a series of data sets

Yes that should work or even

memname eqt 'SALES_'

Respected Advisor
Posts: 4,173

Re: Finding the latest of a series of data sets

Posted in reply to data_null__

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!

Super User
Posts: 10,023

Re: Finding the latest of a series of data sets

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

Respected Advisor
Posts: 4,173

Re: Finding the latest of a series of data sets

Why? It's SELECT MAX(memname)

Super User
Posts: 10,023

Re: Finding the latest of a series of data sets

Never mind, It is my personal favor.

Ask a Question
Discussion stats
  • 8 replies
  • 305 views
  • 6 likes
  • 4 in conversation