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

Hi everybody,

I need help with coding an expansion in my records but I don't know where to start from. So I have the data in the following format:

fyear               anndat          revdat               forecast     analyst

12/31/2000     01/31/2000     01/31/2000          10          a

12/31/2000     01/20/2000     02/10/2000          5            b

12/31/2000     01/20/2000     04/18/2000          4            c

I want to have the following table where each of the records is expanded for the number of periods (months) between anndat and revdat.

fyear               anndat          revdat               forecast     analyst     transaction     monthyear

12/31/2000     01/31/2000     01/31/2000          10          a               1                    01/2000

12/31/2000     01/20/2000     02/10/2000          5              b               2                    01/2000

12/31/2000     01/20/2000     02/10/2000          5               b               2                    02/2000

12/31/2000     01/20/2000     04/18/2000          4               c               3                    01/2000

12/31/2000     01/20/2000     04/18/2000          4               c               3                    02/2000

12/31/2000     01/20/2000     04/18/2000          4               c               3                    03/2000

12/31/2000     01/20/2000     04/18/2000          4               c               3                    04/2000

Any help is appreciated at this point! Thanks a lot in advance Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

data have;

informat fyear anndat revdat mmddyy10.;

input fyear  anndat    revdat      forecast     analyst $;

cards;

12/31/2000     01/31/2000     01/31/2000          10          a

12/31/2000     01/20/2000     02/10/2000          5            b

12/31/2000     01/20/2000     04/18/2000          4            c

;

data want;

  set have;

   do _n_=1 to intck('month',anndat,revdat)+1;

   monthyear=intnx('month',anndat,_n_-1);

  output;

  end;

  format fyear  anndat    revdat mmddyy10. monthyear mmyys7. ;

  proc print;run;

Obs         fyear        anndat        revdat    forecast    analyst    monthyear

  1     12/31/2000    01/31/2000    01/31/2000       10          a        01/2000

  2     12/31/2000    01/20/2000    02/10/2000        5          b        01/2000

  3     12/31/2000    01/20/2000    02/10/2000        5          b        02/2000

  4     12/31/2000    01/20/2000    04/18/2000        4          c        01/2000

  5     12/31/2000    01/20/2000    04/18/2000        4          c        02/2000

  6     12/31/2000    01/20/2000    04/18/2000        4          c        03/2000

  7     12/31/2000    01/20/2000    04/18/2000        4          c        04/2000

View solution in original post

4 REPLIES 4
Linlin
Lapis Lazuli | Level 10

data have;

informat fyear anndat revdat mmddyy10.;

input fyear  anndat    revdat      forecast     analyst $;

cards;

12/31/2000     01/31/2000     01/31/2000          10          a

12/31/2000     01/20/2000     02/10/2000          5            b

12/31/2000     01/20/2000     04/18/2000          4            c

;

data want;

  set have;

   do _n_=1 to intck('month',anndat,revdat)+1;

   monthyear=intnx('month',anndat,_n_-1);

  output;

  end;

  format fyear  anndat    revdat mmddyy10. monthyear mmyys7. ;

  proc print;run;

Obs         fyear        anndat        revdat    forecast    analyst    monthyear

  1     12/31/2000    01/31/2000    01/31/2000       10          a        01/2000

  2     12/31/2000    01/20/2000    02/10/2000        5          b        01/2000

  3     12/31/2000    01/20/2000    02/10/2000        5          b        02/2000

  4     12/31/2000    01/20/2000    04/18/2000        4          c        01/2000

  5     12/31/2000    01/20/2000    04/18/2000        4          c        02/2000

  6     12/31/2000    01/20/2000    04/18/2000        4          c        03/2000

  7     12/31/2000    01/20/2000    04/18/2000        4          c        04/2000

jpapakro
Calcite | Level 5

Hi Linlin,

Thanks a lot for the code! I really appreciate your help!

JP

PGStats
Opal | Level 21

It seems transaction should be 4 for the last group :

data have;
format fyear anndat revdat mmddyy10.;
input (fyear anndat revdat) (:mmddyy10.) forecast analyst $;
datalines;
12/31/2000     01/31/2000     01/31/2000          10          a
12/31/2000     01/20/2000     02/10/2000          5            b
12/31/2000     01/20/2000     04/18/2000          4            c
;

data want(drop=i);
set have;
transaction = intck("MONTH", anndat, revdat) + 1;
format monthYear MMYYS7.;
monthyear = anndat;
do i = 1 to transaction;
     output;
     monthyear = intnx("MONTH",monthyear,1,"SAME");
     end;
run;

proc print; run;

PG

PG
jpapakro
Calcite | Level 5

Hi PGStats,

Thank you very much for the prompt response. 'transaction' is not that important. I just put it there to show that each record is repeated for a certain number of periods. I see your point though, cos' I think you understood that transaction means the number of expanded records.

In any case the code works and I am grateful Smiley Happy

JP

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
  • 4 replies
  • 799 views
  • 5 likes
  • 3 in conversation