SAS Merge and Import

[PetePatel]

Hi all,

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.

Thanks,

Pete

[Kurt_Bremser]

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.