Help using Base SAS procedures

Help on joining/merging one to many

Reply
Contributor
Posts: 24

Help on joining/merging one to many

My datasets looks like this

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

UniqueCompanyCodeDateCompanyName

2)

UniqueCompanyCodeDateMarketCapClosingPrice

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

UniqueCompanyCodeDateMarketCapClosingPrice

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.

Super User
Posts: 19,810

Re: Help on joining/merging one to many

Posted in reply to factorhedge

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.

Contributor
Posts: 24

Re: Help on joining/merging one to many

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.

Respected Advisor
Posts: 4,173

Re: Help on joining/merging one to many

Posted in reply to factorhedge

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.

Contributor
Posts: 24

Re: Help on joining/merging one to many

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

Ask a Question
Discussion stats
  • 4 replies
  • 141 views
  • 0 likes
  • 3 in conversation