topic Re: Help on expand in SAS Procedures

Help on expand

factorhedge — Fri, 07 Jun 2013 11:31:42 GMT

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

Re: Help on expand

esjackso — Fri, 07 Jun 2013 13:59:10 GMT

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!

Re: Help on expand

factorhedge — Mon, 10 Jun 2013 07:47:35 GMT

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

Re: Help on expand

esjackso — Mon, 10 Jun 2013 11:07:58 GMT

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!