topic Re: how to apply derived value from file names to corresponding files in a directory in SAS Programming

how to apply derived value from file names to corresponding files in a directory

William_B — Thu, 07 Mar 2019 20:42:29 GMT

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;

Re: how to apply derived value from file names to corresponding files in a directory

ChrisNZ — Fri, 08 Mar 2019 01:13:56 GMT

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

Re: how to apply derived value from file names to corresponding files in a directory

Tom — Fri, 08 Mar 2019 01:28:22 GMT

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;