Breaking a Row Into Two Different Rows based on Variable

[E_W]

There was recently a change in methodology in how certain programs are categorized. What was before something like this:

County   State             Program   Category          Revenue   Cost    Profit

Drew      Arkansas      P23            Grass Seeds    $100          $80      $20

Has now been divided into two different categories that split the revenue, cost, and profit between the two at predetermined ratios. For P23, it would be 25% towards Wild Grass Seeds and 75% towards Grass/Legume Seeds.

County   State             Program   Category                            Revenue   Cost    Profit

Drew      Arkansas      P23            Wild Grass Seeds            $25          $20      $5

Drew      Arkansas      P23            Grass/Legume Seeds     $75          $60      $15

What would be the function or code needed to trawl through the existing dataset and replace all the existing P23's with the new Categories and figures while keeping other columns like County and State intact? Thank you.

[PaigeMiller]

data want;
    length category $ 40;
    set have;
    if program='P23' then do;
        category='Wild Grass Seeds';
        revenue1=revenue*0.25;
        cost1=cost*0.25;
        profit1=profit*0.25;
        output;
        category='Grass/Legume Seeds';
        revenue1=revenue*0.75;
        cost1=cost*0.75;
        profit1=profit*0.75;
        output;
    end;
    else output;
run;

[E_W]

Thank you, Paige. Is there a way to do this without creating new column names? There are other programs that have data in the same column and I would like to maintain that.

[data_null__ · Accepted solution]

Maybe like this.

data want;
   length category $ 40;
   set have;
   if program='P23' then do;
      _revenue =  revenue;
      _cost    =  cost;
      _profit  =  profit;  

      category =  'Wild Grass Seeds';
      revenue  =  _revenue*0.25;
      cost     =  _cost*0.25;
      profit   =  _profit*0.25;
      output;
      category =  'Grass/Legume Seeds';
      revenue  =  _revenue*0.75;
      cost     =  _cost*0.75;
      profit   =  _profit*0.75;
      output;
      end;
   else output;
   drop _:;
   run;

[PaigeMiller]

---

@E_W wrote:

Thank you, Paige. Is there a way to do this without creating new column names? There are other programs that have data in the same column and I would like to maintain that.

---

After you create a data set with the new column names, you can move the values into the old column names and then delete the new column names.