Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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:

PortfolioDatePeriod
a3/1/20130
a3/2/20131
a3/3/20132
b5/1/20130
b6/1/20131

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:

PortfolioDatePeriod
a3/1/20130
a3/2/20131
a3/3/20132
a3
a4
b5/1/20130
b6/1/20131
b2
b3
b4

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

thanks!


Accepted Solutions
Solution
‎11-08-2013 12:22 PM
Trusted Advisor
Posts: 1,137

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,

Jagadish

Thanks,
Jag

View solution in original post


All Replies
Solution
‎11-08-2013 12:22 PM
Trusted Advisor
Posts: 1,137

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,

Jagadish

Thanks,
Jag
Regular Contributor
Posts: 168

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=_Smiley Happy;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

Respected Advisor
Posts: 3,141

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.

Need further help from the community? Please ask a new question.

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