BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Haikuo
Onyx | Level 15

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.

pakms
Calcite | Level 5

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 Smiley Happy

Some codes might not directly solve my problem, but the sample codes did polish my SAS coding skill.

Thanks to all

art297
Opal | Level 21

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;

pakms
Calcite | Level 5

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

art297
Opal | Level 21

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;

Amir
PROC Star

Hi,

Were you looking for something like the attached?

Regards,

Amir.

pakms
Calcite | Level 5

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

Amir
PROC Star

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.

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
  • 22 replies
  • 1653 views
  • 6 likes
  • 4 in conversation