08-15-2014 03:39 PM
I am pulling one field from a monthly file into a much larger flat file. This files name changed every month. Looking at the history there is inconsistency in the naming convention of the month. I told the end user to knock it off but it raised a question in my head. I wonder if there is way to only pull a substring of a folder. I'll put an example below:
is there a way to pull only the first charachter as a substring for month, formatted as month.?
I have the date macro for month formatted as month. but I haven't been able to pull the string in the filename.
08-15-2014 04:05 PM
I wonder if there is way to only pull a substring of a folder.
%let monthpart= %scan( &pathandfile, -2, \ ) ;
part -1 would be the filename
and if you want that number that comes before the month abbr, (and assuming the number would become 10, 11 and 12)
%let num_part = %scan(&monthpart, 1, %str(-\) ) ;
I use %str() to protect that - from being mistaken for part of a formula
08-16-2014 07:30 PM
Is just the third folder name that is giving you problems?
Are you trying to convert the inconsistent date descriptions into dates?
Or are you trying to find the files based on a specific date request?
How are you getting the file names? Are they passed to you as a macro variable? As data in a text file or existing dataset?
If you have the name already in a string then SCAN() is a good tool. You can even truncate the month part at three characters using SUBSTR().
Let's play with your examples in a simple data step.
data dirs ;
input filename $80.;
date = input(catx('-',scan(last_folder,1,'-')
format date yymmdd10.;
put (_n_ _all_) (=/);
NOTE: The data set WORK.DIRS has 2 observations and 4 variables.