04-19-2013 10:14 AM
My datasets looks like this
1) It has many columns but for simplicity sake I have mentioned 3 columns
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
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.
04-19-2013 10:56 AM
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.
04-20-2013 11:08 AM
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.
04-19-2013 07:33 PM
There is some quite good documentation in the support.sas.com domain about merging SAS data sets
And here the example code which might be helpful for your case
There is similar documentation for combining data using SAS SQL - just can't find the link right now.