06-07-2013 07:31 AM
Hi my data set look like this
|Company Name||CompanyId||Date||Networth ($Mn)|
I would like to expand the data set into monthly from annually. I have another table which has monthly data , I would like to left join the Networth variable but I want the annual Networth values to populate monthly so that I can compute the ratios with Networth in the numerator. Could someone help me
06-07-2013 09:59 AM
If I understand correctly ...
It probably depends on how the characteristics of your monthly date variable. If its an actual date numeric variable I believe you could you not just join on companyname and the year(monthdatevar) (this also assumes your year var in the annual file is numeric). If they are character variables then you might have to figure out the substring to get the year join to work.
Hope this helps!
06-10-2013 03:47 AM
actually my monthly date is a numeric and it is YYYYMM format , my left table is the superset which has month level data but in my right table the variable 'networth' is occur few times in a year , I would like to create a table using left join but the column I am joining to the left table has 'Networth ' which needs to be populated same for all the the months
CompanyName Monthend MarketCap
CompanyName Year Networth
Post merging I want the 2001 networth to be populated for all my Monthend months
I want the final table like this
CompanyName Monthend MarketCap Networth
06-10-2013 07:07 AM
Still dont understand why the following would not work:
create table out as
select a.companyname, a.monthend, a.marketcap, b.networth
from lefttable as a left join righttable as b
on a.companyname = b.companyname and year(a.monthend) = b.year
Unless you are saying the monthend is numeric but not an actually datetime varaible (when not formatted the actual value is 200203 and not something like 15430 ). In this case I believe you could change the on statement to:
on a.companyname = b.companyname and int(a.monthend/100) = b.year
Hope this helps!