Help on expand

[factorhedge]

Hi my data set look like this

Company Name	CompanyId	Date	Networth ($Mn)
A		2004
A		2005
A		2006
B		2002
B		2003

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

[esjackso]

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!

EJ

[factorhedge]

Thanks ,

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

Left table

CompanyName Monthend MarketCap

A                    200203    

A                    200204

A                    200205

......

......

......

Right Table

CompanyName     Year      Networth

A                         2001

A                         2002

A                         2003

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

A                      200203                             

A                      200204

A                      200205

[esjackso]

Still dont understand why the following would not work:

Proc sql;

     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

     ;

quit;

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!

EJ