Hi all!
I would have to use a dataset that is provided by another unit. last part of the dataset name(day part) changes each month, and rest stays the same. such as
datasetxyz_20180523
datasetxyz_20180630
datasetxyz_20180710
I want my code to pick up the correct dataset when I run. If I am running on 2018 may, it should grab the dataset --> datasetxyz_20180523.
Please help me how to achieve this.
Thanks in advance
Query dictionary.tables:
%let libname=mylib;
%let dsname=datasetxyz_;
%let period=201805;
proc sql;
select memname into :memname from dictionary.tables
where substr(memname,1,length("&dsname&period")) = upcase("&dsname&period") and libname = upcase("&libname");
quit;
data want;
set &libname..&memname;
run;
Why should it pick up that one for 2018? Is it always the first. As a recommendation, which will be ignored, putting data - in this case a date - in dataset names will only lead to far harder programming to handle it. One dataset with date as a column is so much simpler to work with from any angle, its then just a matter of
where date_var="23May2018"d;
As it is, now your going to have to write code which delves in the metadata, converts the various information, then pulls the right file out. Factoring up your work, the code you write, and the likelihood that it will fail 10 fold.
Maybe something like:
data inter; set sashelp.vtable where=(libname="<YOURLIB>" and substr(memname,1,10)="DATASETXYZ")); d_var=input(scan(memname,2,"_"),yymmdd8.); where year(d_var)=2018; run; proc sort data=inter; by d_var; run;
data inter;
set inter;
if _n_=1;
run;
So this gets a list of datasets from a library which you need to specify (all uppercase), with that dataset prefix, gets the date part from the filename, and converts to a date, keeping only year 2018. Then it sorts the data with earliest first and finally takes the first record. All that code just because of a design choice, madness.
Hello,
data datasetxyz_20180602;
set sashelp.class;
run;
proc sql noprint;
SELECT MEMNAME
INTO :dsname
FROM dictionary.tables
WHERE LIBNAME="WORK" AND MEMNAME LIKE cats("DATASETXYZ_",put(today(),yymmn6.),"%");
quit;
%put &dsname.;
Query dictionary.tables:
%let libname=mylib;
%let dsname=datasetxyz_;
%let period=201805;
proc sql;
select memname into :memname from dictionary.tables
where substr(memname,1,length("&dsname&period")) = upcase("&dsname&period") and libname = upcase("&libname");
quit;
data want;
set &libname..&memname;
run;
If you only get one dataset per month, there is a fast and dirty solution. Just wildcard the day part with a colon:
%let month=201806;
/* or perhaps: %let month=%sysfunc(date(),yymmn6.); */
Data want
set <libname>.datasetxyz_&month: ;
Of course, if you get more than one dataset each month, this will read all of the datasets for the month. Which may not be what you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.