SAS Merge and Import

Reply
Contributor
Posts: 26

SAS Merge and Import

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

 

Super User
Posts: 10,280

Re: SAS Merge and Import

Posted in reply to PetePatel

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.

 

 

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 1 reply
  • 93 views
  • 0 likes
  • 2 in conversation