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
How did you add year1, and what does the log of both steps look like (use the {i} icon for posting logs)?
How did you add year1, and what does the log of both steps look like (use the {i} icon for posting logs)?
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;
There's no access to market in your proc sql. Without seeing all the code, there's no way to diagnose it.
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.
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.
Thanks, KurtBremser
i think it may be something wrong with my datasets. and i converted the character values for 'stkcd' to numeric values and it worked.
i would care of the way in which i post my question in future.
Jane
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.