topic Help on joining/merging one to many in SAS Procedures

Help on joining/merging one to many

factorhedge — Fri, 19 Apr 2013 14:14:07 GMT

My datasets looks like this

1) It has many columns but for simplicity sake I have mentioned 3 columns

UniqueCompanyCode	Date	CompanyName

UniqueCompanyCode	Date	MarketCap	ClosingPrice

I would like to create a table by joining/merging the above two tables based on ' UniqueCompanyCode' and 'Date' I would want the output like this . Essentially table 1 is monthly data for 12 years(there are 144 different sreadsheets) and it has many other columns and table 2 is one spreadsheet having all the companies data monthly for 12 years. I am looking to augment the MarketCap and ClosingPrice date from table 2 to table 1

UniqueCompanyCode	Date	MarketCap	ClosingPrice

For example if the date in table 1 is 28/02/2012 and UniqueCompanyCode is " 1234 " than I would want merge the respective data from table 2

I would like to know the code for doing for 1 month , after which I guess I will have to create a ' do loop ' for all other sheets.

Re: Help on joining/merging one to many

Reeza — Fri, 19 Apr 2013 14:56:54 GMT

Bring in your monthly data and append it all together first. Then merge it all at once with table 2.

1. Import all your spreadsheets (there are ways to automate many imports).

2. Append them together.

3. Merge once with Table 2.

This way you separate your import and merge process and it helps to debug things faster.

Re: Help on joining/merging one to many

Patrick — Fri, 19 Apr 2013 23:33:46 GMT

There is some quite good documentation in the support.sas.com domain about merging SAS data sets

Step-by-Step Programming with Base SAS(R) Software

And here the example code which might be helpful for your case

Step-by-Step Programming with Base SAS(R) Software

There is similar documentation for combining data using SAS SQL - just can't find the link right now.

Re: Help on joining/merging one to many

factorhedge — Sat, 20 Apr 2013 15:04:06 GMT

thanks Patrick , really useful links for a newbie like me, cleared lot of my doubts on merging, combining

Re: Help on joining/merging one to many

factorhedge — Sat, 20 Apr 2013 15:08:49 GMT

Thanks Reeza , very helpful , however before I append all the sheets I just wanted to see whether the code suits my required output. There are around 155 such sheets with 35k rows in each and 13-15 columns.

Perhaps I will do as you said , that is I have a macro to import all sheets then will append and merge it using by. I will have to merge it by dates and Companycodes.