My bad, I took your data for granted, not checking the details, the month of your 'datadate' actually have been seen in every possible month of a year, from Jan to Dec, all of them. Here is another attempt, let me know how it works:
proc sql noprint;
create table want as
select a.cusip6, sum(a.meet) as FREQ,
calculated freq/count(distinct a.FPEDATS) as PERCENT_MEET format=percent6.2,
b.datadate
from (select distinct cusip6, fpedats, meet,datadate from qtrforecast) a
inner join
(select distinct cusip6, datadate from qtrforecast(where=(not missing(datadate)))) b
on a.cusip6=b.cusip6 and intnx('qtr',b.datadate,-3,'b') <= a.fpedats <= b.datadate
group by a.cusip6, b.datadate
;
quit;
Haikuo
Updated using quarter instead of year.
Thanks to Haikuo for the correct code - which can provide a good solution for my problem.
I also wish to say thank you to all the responses that help me to solve my problem
Some codes might not directly solve my problem, but the sample codes did polish my SAS coding skill.
Thanks to all
Since you only want the last 4 (full?) quarters, I'd suggest using a DOW loop. Does the following result in what you want?:
data want;
format fydate date9.;
do until(last.cusip6);
set qtrforecast;
by cusip6;
if not missing(datadate) then fydate=datadate;
end;
do until(last.cusip6);
set qtrforecast;
by cusip6;
if fpedats eq INTNX('QTR', fydate, -3,'e') then qtr=1;
else if fpedats eq INTNX('QTR', fydate, -2,'e') then qtr=2;
else if fpedats eq INTNX('QTR', fydate, -1,'e') then qtr=3;
else if fpedats eq INTNX('QTR', fydate, -0,'e') then qtr=4;
else call missing(qtr);
if not missing(qtr) then output;
end;
run;
Hi Art,
Thanks for your program, but it still not help in solving problem.
Your program help me to clean up data, especially you helped me to fix the financial year for the qtr1-3. In addition, you helped me to create an additional quarters column.
The problem that I found that the output data only contains the LAST financial year for each firm. For example, the cusip6 with 000360, only quarterly data for financial year-end of 31DEC2010 is available. I also wish to generate a quarterly data for financial years from 2003 instead.
My desired output is that:
For the cusip6 of "000360", the following should be shown:
cusip6 FREQ PERCENT_MEET DATADATE
000360 1 0.25 31DEC2003
000360 1 0.25 31DEC2004
000360 1 0.25 31DEC2005
000360 3 0.75 31DEC2006
000360 3 0.75 31DEC2007
000360 4 1.00 31DEC2008
000360 3 0.75 31DEC2009
000360 1 0.25 31DEC2010
Thank you.
pakms
This still won't solve all of your problems. I had thought that you only wanted the most recent 4 quarters. The following gets all of them but, as you will see, will give you some odd looking results when a company changes its fiscal year. Hopefully, though, you can figure out how to extract just the records you need:
proc sort data=qtrforecast out=temp nodupkey;
by cusip6 fpedats;
run;
data temp;
set temp;
by cusip6;
if first.cusip6 then do;
fygroup=1;
output;
end;
else if not missing(datadate) then do;
output;
fygroup+1;
end;
else output;
run;
data want;
format fydate date9.;
do until(last.fygroup);
set temp;
by cusip6 fygroup;
if not missing(datadate) then fydate=datadate;
end;
do until(last.fygroup);
set temp;
by cusip6 fygroup;
if fpedats eq INTNX('QTR', fydate, -3,'e') then qtr=1;
else if fpedats eq INTNX('QTR', fydate, -2,'e') then qtr=2;
else if fpedats eq INTNX('QTR', fydate, -1,'e') then qtr=3;
else if fpedats eq INTNX('QTR', fydate, -0,'e') then qtr=4;
else call missing(qtr);
if not missing(qtr) then output;
end;
run;
Hi,
Were you looking for something like the attached?
Regards,
Amir.
Dear Amir,
Your SAS output is the correct answer too.
I do accept any financial year without all quarters' forecast available. If any financial year with only 3 forecasts with 2 "meet", then the percent_meet = 2/3 = 0.67 or 67.7%. Therefore, it is not necessary that all firms with all 4 qtr forecasts.
Thank you for your sincere helps.
Regards,
Pakms
Hi,
Looks like I posted the wrong code last time. How about the following:
data want(keep=cusip6 freq percent_meet datadate);
set tmp1.Qtrforecast;
by cusip6;
retain freq 0
old_datadate 0
meet_q0 0 /* quarter before quarter 1 */
qtr 0
;
qtr+1;
meet_q0=lag4(meet);
if qtr gt 4 and meet_q0 and freq then
freq+(-1);
freq+sum(0,meet);
if datadate ne . then
if datadate=old_datadate then
do;
link reset;
delete;
end;
else
do;
percent_meet=freq/4;
output;
link reset;
end;
if last.cusip6 then
link reset;
return;
reset:
freq=0;
old_datadate=datadate;
qtr=0;
return;
run;
Regards,
Amir.
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 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.