Using DMY dates in filenames makes this unnecessarily hard, so I recommend to change this to dates in YMD order as a first step. This will make working with your datasets in the future much easier.
With a sensible date, it's easy by using wildcards:
data all;
set xyz_2022:;
run;
The SET statement which I posted will add all currently present datasets that begin with XYZ_2022.
@Vasundha wrote:
can't be renamed them.
Bullshit (sorry, but...). You can always improve your data designs, and renaming NOW is little work compared to the hassles you will always have in the future with such STUPID file/dataset names.
If I had tolerated such sub-optimals in my professional life, my time would have been swamped by unnecessary and tedious extra work, keeping me from doing useful things, or finishing them in time (or simply having a beer for celebrating good work done). Correct your design issue NOW, continuing the current state will only cause more work when you finally cannot go on any further with the current situation.
Changing the names once isn't so hard at all:
%let lib=library; /* set your library name here */
data _null_;
call execute("proc datasets lib=&lib.;change");
do date = '01jan2022' to today(); /* you can start here with the earliest date overall */
olddate = put(date,date9.);
newdate = put(date,yymmddn8.);
call execute (" xyz_" !! olddate !! "=xyz_" !! newdate);
end;
call execute(";quit;");
run;
You can use a similar approach to do the appending, but I STRONGLY, STRONGLY, STRONGLY recommend against keeping the current name structure.
Please post the complete log of the DATA _NULL_ and the subsequently generated PROC DATASETS.
Everything that @Kurt_Bremser says. But should you be lucky enough that the Xyz_... "root" name of your tables will only match the tables you want to concatenate then you might get away with below code.
Please note: This will just be a "make it work" but not a clean solution.
/* create sample data */
options dlcreatedir;
libname have "%sysfunc(pathname(work))\have";
data have.Xyz_25MAR2022 have.Xyz_26MAR2022;
set sashelp.class;
run;
/* create Want table with all daily tables concatenated */
data want;
set have.Xyz_:;
run;
This is a macro solution:
data xyz_01jan2022;
set sashelp.class;
run;
data xyz_02jan2022;
set sashelp.class;
run;
%let lib=WORK; /* set your library name here */
%macro combine;
data want;
set
%do date = %sysevalf('01jan2022'd) %to %sysfunc(today());
%let dt = %sysfunc(putn(&date.,date9.));
%if %sysfunc(exist(&lib..xyz_&dt.))
%then %do;
&lib..xyz_&dt.
%end;
%end;
;
run;
%mend;
%combine
Now compare that to the simplicity of using a wildcard (and add in the fact that YMD dates will always sort properly, so selecting files that are old enough for deleting/archiving or "new" enough for processing is done in a single comparison), and you see how bad the current design is.
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 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.