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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.