DATA Step, Macro, Functions and more

How to repeat the entire row in a dataset with change in one variable based on a condition

Reply
Contributor
Posts: 20

How to repeat the entire row in a dataset with change in one variable based on a condition

I want to repeat the entore row in a SAS data set based on the following condition :

 

 

  • If the 'duration' of the fund is lesser then equal to 4 and type of 'base_fund' is Platinum or blank value ( which implies platinum), I need to generate additional 4 rows same as platinum row values, but changing the "base_fund' to these 4 funds 'Add_Silver', 'Silver'. 'Bronze','Add_gold'. These needs to be repeated twice based on type 'RO' and 'OT'.

 

I have attached the screen shot on how the raw data looks like and what I need as output for Reg_number : 8765432:

 

Data I have:

 

Want.png

 

Data I want Want.png

 

I tried using repeat function and SQL, however I was not fully successful, Appreciate your quick help on this.

Super User
Posts: 17,784

Re: How to repeat the entire row in a dataset with change in one variable based on a condition

Use a Data step with an explicit OUTPUT statement. Make sure to include an else output so that other records are also displayed. 

 

If <condition> then do;
    Do base_fund= 'add_silver' , 'add_bronze', ... 'Add_awesome';
         Output;
    End;
End;
Else output;
Trusted Advisor
Posts: 1,372

Re: How to repeat the entire row in a dataset with change in one variable based on a condition

If by "repeat" you mean to duplicate a row with changes then the code should be:

 

Data want;

 set have;

        output;  /* save original row */

       if   <condition> then do;

           <do your changes>

          output;     /* with the changed data */

      end;

run;

Frequent Contributor
Posts: 83

Re: How to repeat the entire row in a dataset with change in one variable based on a condition

Data want;   set have;

Output;

If duration le 4 then do;

   Basefund=’add_silver’;   output’

   Basefund=’silver’;   output’

   Basefund=’add_gold’;   output’

   Basefund=’bronze’;   output’

End;

 

 

 

JIm

Ask a Question
Discussion stats
  • 3 replies
  • 349 views
  • 1 like
  • 4 in conversation