Hi,
looking for some help as how to apply the derived variable values from file names. I feel like there is an easy macro to do this and my mind is just not going there.
This code generates two variables - one with the path/filename the other the corresponding file date.
I want to insert the file date value with the dataset of the corresponding file name as a variable to be associated with each observation in the particular file.
filename examples
xxxxxxxxxxxxxxxx_dec2018
xxxxxxxxxxxxxxxx_jan2019
xxxxxxxxxxxxxxxx_feb2019
filename fname pipe 'dir C:\files\*.sas7bdat /b/s';
data test;
infile fname;
input filename :&$100.;
new_var=substr(filename,prxmatch('/_\d{8}\./', filename)+31,7);
output;
run;
data test2;
set test;
date1=input(new_var,anydtdte7.);
lastDay=intnx ('month',date1,0,'E');
filedate=lastday;
format filedate date9.;
keep filename filedate;
run;
The data step can know the name of the dataset that is contributing the current observation. Use the INDSNAME= option on the SET statement.
For example here is a way to combine all of the "monthly" datasets into a single dataset and add the month as a date variable.
libname files 'C:\Files';
proc contents noprint data=files._all_ out=contents; run;
proc sql noprint;
select distinct catx('.',libname,memname)
into :dslist separated by ' '
from contents
;
quit;
data want ;
length filedate 8 dsname $50 ;
set &dslist indsname=dsname ;
filedate = input(cats('01',scan(dsname,-1,'_')),date9.);
format filedate date9.;
run;
Why are you looking for 8 digits when the filename doesn't have them?
This works:
data TEST;
input FILENAME :&$100.;
DATEC = substr(FILENAME,prxmatch('/_\w{3}\d{4}\./', FILENAME)+1);
DATEN = input(DATEC,anydtdte7.);
FILEDATE= intnx ('month',DATEN,0,'E');
put FILEDATE= date9.;
cards;
xxxxxxxxxxxxxxxx_JAN2019.
run;
FILEDATE=31JAN2019
The data step can know the name of the dataset that is contributing the current observation. Use the INDSNAME= option on the SET statement.
For example here is a way to combine all of the "monthly" datasets into a single dataset and add the month as a date variable.
libname files 'C:\Files';
proc contents noprint data=files._all_ out=contents; run;
proc sql noprint;
select distinct catx('.',libname,memname)
into :dslist separated by ' '
from contents
;
quit;
data want ;
length filedate 8 dsname $50 ;
set &dslist indsname=dsname ;
filedate = input(cats('01',scan(dsname,-1,'_')),date9.);
format filedate date9.;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.