BookmarkSubscribeRSS Feed
LHV
Calcite | Level 5 LHV
Calcite | Level 5

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.

8 REPLIES 8
data_null__
Jade | Level 19

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_

Patrick
Opal | Level 21

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

LHV
Calcite | Level 5 LHV
Calcite | Level 5

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,

data_null__
Jade | Level 19

Yes that should work or even

memname eqt 'SALES_'

Patrick
Opal | Level 21

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!

Ksharp
Super User

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

Patrick
Opal | Level 21

Why? It's SELECT MAX(memname)

Ksharp
Super User

Never mind, It is my personal favor.

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
  • 8 replies
  • 1010 views
  • 6 likes
  • 4 in conversation