03-15-2018 08:59 AM
I have a SAS Dataset (A) containing c. 50k unique keys that I would like to merge to a specific Excel files (B) depending on month and year.
SAS Dataset (A) includes; unique 'key' (example format: A12345) and 'date opened' (example format: 29/04/2015).
(B) is essentially a folder structure with several years and months with example as follows: 2015>April. Inside the April folder is an Excel called April 15 and includes 'score'.
How can I get SAS to locate the correct month from the folder structure (depending on date opened), import it and merge to it, so I end up with something like this where score score is appended to Dataset A for each unique key:
Key (A1234) data opened (29/04/2015) score (10)
Key (B123) data opened (13/08/2017) score (6)
Any help would be greatly appreciated.
03-15-2018 09:50 AM
Step 1: set up a directory and filename structure that makes sense, using ISO-formatted dates (YYYY-MM-DD). That makes the subsequent steps easier.
Step 2: import all files from there into one contiguous SAS dataset; add the date from the name of the individual file as a variable to the dataset
Step 3: join along keys and the newly created variable.