BookmarkSubscribeRSS Feed
factorhedge
Fluorite | Level 6

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

3 REPLIES 3
esjackso
Quartz | Level 8

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
Fluorite | Level 6

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 709 views
  • 2 likes
  • 2 in conversation