BookmarkSubscribeRSS Feed
factorhedge
Fluorite | Level 6

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.

4 REPLIES 4
Reeza
Super User

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.

factorhedge
Fluorite | Level 6

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.

Patrick
Opal | Level 21

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.

factorhedge
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 688 views
  • 0 likes
  • 3 in conversation