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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 997 views
  • 0 likes
  • 3 in conversation