Help using Base SAS procedures

Help on expand

Reply
Contributor
Posts: 24

Help on expand

Hi my data set look like this

Company NameCompanyIdDateNetworth ($Mn)
A2004
A2005
A2006
B2002
B2003

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

Super Contributor
Posts: 333

Re: Help on expand

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

Contributor
Posts: 24

Re: Help on expand

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

Super Contributor
Posts: 333

Re: Help on expand

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

Ask a Question
Discussion stats
  • 3 replies
  • 156 views
  • 2 likes
  • 2 in conversation