DATA Step, Macro, Functions and more

proc left join

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

proc left join

Hi,

AS i need to calculated the opening price-deflated earnings which is (net profits t /price t-1) within groups.

the price would be the value of previous year. the net profit is current value.

 

 the data in my profit table(containing net profit) is like:

 stkcd    year     net profit

 00001   1991    4000

 00001   1992    4500

 00001   1993    3800

 00002   1991    2000

 00002   1992    3000

........

 

and the data in the market table (containing the opening price) is like

 stkcd    year     price

 00001   1991    20

 00001   1992    25

 00001   1993    30

 00002   1991    10

 00002   1992    20

 

i want the get the result like :

stkcd    year     net profit  price 

 00001   1991    4000         .

 00001   1992    4500        20

 00001   1993    3800        25

 00002   1991    2000        .

 00002   1992    3000         20

 

i have add a variable (year1 )  which is equal to year+1 to the market table so that i can identify the deflated price value.

and try the code like:

 

proc sql;
create table h2 as
select profit.*,market.price as lagprice
from profit  left join market
on profit.stkcd=market.stkcd and profit.year=market.year1;
quit;

 

it have processed without error but in the last table, the price values are all missing.

 

hope you can help me with the problem! thank!

 

Jane

 

 

 

 


Accepted Solutions
Solution
‎10-08-2017 01:53 AM
Super User
Posts: 7,791

Re: proc left join

How did you add year1, and what does the log of both steps look like (use the {i} icon for posting logs)?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎10-08-2017 01:53 AM
Super User
Posts: 7,791

Re: proc left join

How did you add year1, and what does the log of both steps look like (use the {i} icon for posting logs)?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 48

Re: proc left join

Posted in reply to KurtBremser

Sorry for clicking the wrong button.

 

i just add the year1 using the data step: 

data market;
set market;
year1= year +1;
run;

 

and the pro log is : 

35 proc sql;
36 create table h2
37 as select pro9.*,trd_year5.Yopnprc as lagYopnprc
38 from w1.pro9 left join w1.trd_year5
39 on pro9.stkcd=trd_year5.stkcd and pro9.year=trd_year5.year1;


NOTE:  WORK.H2  has been created,there are 35376 rows,66 colmuns。

40 quit;

Super User
Posts: 7,791

Re: proc left join

There's no access to market in your proc sql. Without seeing all the code, there's no way to diagnose it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,791

Re: proc left join

And follow my advice for posting logs.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 48

Re: proc left join

Posted in reply to KurtBremser

OK, the log for proc step  is 

11
12 proc sql;
13 create table h2
14 as select pro8.*,trd_year4.Yopnprc as lagYopnprc
15 from w1.pro8 left join w1.trd_year4
16 on pro8.stkcd=trd_year4.stkcd and pro8.year=trd_year4.year1;


NOTE: the  WORK.H2 has been created ´there are 35596 rows 6 columns.

i drop the processing time.

 

thanks.

Super User
Posts: 7,791

Re: proc left join

This is getting nowhere.

Post examples for your datasets pro8 and trd_year4.

Use datasteps with datalines, so we can easily recreate your datasets with copy/pasting and submit.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 48

Re: proc left join

Posted in reply to KurtBremser

Thanks, KurtBremser

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 116 views
  • 0 likes
  • 2 in conversation