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

Solved
Occasional Contributor
Posts: 8

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

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!

Accepted Solutions
Solution
‎11-08-2013 12:22 PM
Posts: 1,147

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

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,

Thanks,
Jag

All Replies
Solution
‎11-08-2013 12:22 PM
Posts: 1,147

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

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,

Thanks,
Jag
Regular Contributor
Posts: 171

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

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

Posts: 3,167

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

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

Occasional Contributor
Posts: 8

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

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 !

🔒 This topic is solved and locked.

Discussion stats
• 4 replies
• 262 views
• 9 likes
• 4 in conversation