BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
William_B
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

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

 

Tom
Super User Tom
Super User

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 967 views
  • 0 likes
  • 3 in conversation