04-24-2012 06:45 PM
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.
set SOURCE.SALES_201204(keep=var1 var2 var3);
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.
%let YYYYMM =%sysfunc(putn( %sysfunc(today()),yymmn6.));
%let data_set = SOURCE.SALES_&YYYYMM;
%if %sysfunc(exist(&data_set.)) %then;
%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;
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.
04-24-2012 06:50 PM
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;
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';
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
66 %put NOTE: DATA_SET=&data_set;
Message was edited by: data _null_
04-24-2012 07:16 PM
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%'
04-24-2012 07:23 PM
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.
proc sql noprint;
select max(memname) into :data_set
where libname eq 'SOURCE' and memname like 'SALES%';
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,
04-24-2012 10:01 PM
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!
04-25-2012 12:51 AM
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 ;