topic Re: Breaking a Row Into Two Different Rows based on Variable in SAS Programming

Breaking a Row Into Two Different Rows based on Variable

E_W — Thu, 31 Oct 2024 17:42:11 GMT

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.

Re: Breaking a Row Into Two Different Rows based on Variable

PaigeMiller — Thu, 31 Oct 2024 17:57:16 GMT

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;

Re: Breaking a Row Into Two Different Rows based on Variable

E_W — Thu, 31 Oct 2024 18:35:25 GMT

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.

Re: Breaking a Row Into Two Different Rows based on Variable

data_null__ — Thu, 31 Oct 2024 21:14:19 GMT

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;

Re: Breaking a Row Into Two Different Rows based on Variable

PaigeMiller — Thu, 31 Oct 2024 21:22:34 GMT

@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.