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.
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_
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%'
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,
Yes that should work or even
memname eqt 'SALES_'
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!
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
Why? It's SELECT MAX(memname)
Never mind, It is my personal favor.
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.
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.