Desktop productivity for business analysts and programmers

Duplicating rows following condition

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Duplicating rows following condition

[ Edited ]

Hi,

 

I have a dataset like this:

 

DataA DataB DataC

ajbg   34   31/01/2000

hytv   20   31/08/1995

ytffg   54   28/02/2005

...

 

I would like to duplicate rows for each row. How many times? Exactly the number of time of DataB: 34 times for the first row, 20 for the second one, 54 for the third, in the way we have something like that at the end.

 

DataA DataB DataC

ajbg   34   31/01/2000

ajbg   33   29/02/2000

ajbg   32   31/03/2000

ajbg   31   30/04/2000

ajbg   30   31/05/2000

...

hytv   20   31/08/1995

hytv   19   30/09/1995

hytv   18   31/10/1995

hytv   17   30/11/1995

hytv   16   31/12/1995

hytv   15   31/01/1996

...

ytffg   54   28/02/2005

ytffg   53   31/03/2005

ytffg   52   30/04/2005

ytffg   51   31/05/2005

ytffg   50   30/06/2005

...

 

Is there a way to do this? Thanks for help, I am only a beginner on SAS


Accepted Solutions
Solution
‎06-03-2016 10:27 AM
Grand Advisor
Posts: 10,210

Re: Duplicating rows following condition

Assuming your dates for datac are actually SAS date values and not characters perhaps:


data want;
   set have ;
   do datab=datab to 1 by -1;
      output;
      datac = intnx('month',datac,-1,'e');
   end;
run;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Duplicating rows following condition

Yes, do loops:

data want;
  set have;
  do i=0 to datab;
    datac=datac+i;
    output;
  end;
run;

This creates a loop going from 0 to your number.  The record is then output once for each of these loops, so the first time it will be datac+0 -> output, then datac+1 output etc.

Solution
‎06-03-2016 10:27 AM
Grand Advisor
Posts: 10,210

Re: Duplicating rows following condition

Assuming your dates for datac are actually SAS date values and not characters perhaps:


data want;
   set have ;
   do datab=datab to 1 by -1;
      output;
      datac = intnx('month',datac,-1,'e');
   end;
run;
Trusted Advisor
Posts: 1,114

Re: Duplicating rows following condition

To obtain ascending months, simply replace -1 with 1 in the third argument of the INTNX function used in ballardw's approach.

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 248 views
  • 0 likes
  • 4 in conversation