topic Re: insert additional rows when the number of rows are less than a value in SAS Procedures

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

sweetgorgor — Fri, 08 Nov 2013 15:33:38 GMT

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!

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

Jagadishkatam — Fri, 08 Nov 2013 17:22:19 GMT

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

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

sam369 — Sat, 09 Nov 2013 06:23:12 GMT

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

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

Haikuo — Sat, 09 Nov 2013 17:02:36 GMT

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

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

sweetgorgor — Tue, 12 Nov 2013 21:20:24 GMT

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 !