insert additional rows when the number of rows are less than a value

[sweetgorgor]

Hi guys,

I am trying to insert some rows with certain fields when the last period by portfolio is < 5. For example, the current table looks like this:

Portfolio	Date	Period
a	3/1/2013	0
a	3/2/2013	1
a	3/3/2013	2
b	5/1/2013	0
b	6/1/2013	1

Where i would want to create 2 more rows for portfolio a and 3 more rows for portfolio b and output the portfolio and period, so the end table would look like this:

Portfolio	Date	Period
a	3/1/2013	0
a	3/2/2013	1
a	3/3/2013	2
a		3
a		4
b	5/1/2013	0
b	6/1/2013	1
b		2
b		3
b		4

can someone help please? i tried to do a do loop with the last statement and retain the period, but it's not working .

thanks!

[Jagadishkatam · Accepted solution]

data have;

    input Portfolio$    Date $     Period;

cards;

a    3/1/2013    0

a    3/2/2013    1

a    3/3/2013    2

b    5/1/2013    0

b    6/1/2013    1

;

proc sort data=have out=dummy(keep=Portfolio) nodupkey;

    by     Portfolio;

run;

data dummy_;

    set dummy;

    do period=0 to 4;

    output;

    end;

run;

proc sort data=    dummy_;

    by     Portfolio  Period;

run;

proc sort data=    have;

    by     Portfolio  Period;

run;

data want;

    merge have dummy_;

    by     Portfolio Period;

run;

Thanks,

Jagadish

[sam369]

Hi ,

It is same as jagadish approach... but a little different way. i am using jagadish have dataset.

data have;

    input Portfolio$    Date $     Period;

cards;

a    3/1/2013 0

a    3/2/2013 1

a    3/3/2013 2

b    5/1/2013 0

b    6/1/2013 1

;

run;

data classdata;

do period=0 to 4;

  output;

end;

run;

proc summary data=have classdata=classdata nway;

by portfolio;

class period;

output out=temp(drop=_:);run;

proc sql;

create table want as

select a.*,b.date

from have as b right join temp as a

on a.portfolio=b.portfolio & a.period=b.period;

quit;

Thanks

Sam

[Haikuo]

If the incoming data came in sorted like the presented sample, here is an one-step data step solution:

data have;

    input Portfolio$    Date $     Period;

cards;

a    3/1/2013 0

a    3/2/2013 1

a    3/3/2013 2

b    5/1/2013 0

b    6/1/2013 1

;

data want;

  set have;

   by Portfolio period;

   output;

    if last.Portfolio then

        do period=period+1 to 4;

          call missing(date);

            output;

        end;

run;

Haikuo

[sweetgorgor]

tried all 3, and they all work! Thanks a bunch for the help guys! Sorry couldn't get back to you guys as I was on vacation! Thanks again !