Create a row total in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Create a row total in SAS

I have a dataset like this:

Year           Type    Income1  Income2

200101         A      500         200

200101         A      700         300

200201         A      600         400

200201         A      300         500

200101         B      400         600

200101         B      700          800

200201         B      600          900

200201         B       400          400

I need a report like this:

            TYPE                            TYPE

              A                                  B                              TOTAL

YEAR      Income1 Income2        Income1  Income2        Income1    Income2

200101      1200       500             1100         1400            3300          1900

200201      1100       900             1000          1300           2100           2200

Total          3300      1400            2100          2700           5400           4100

Is there any simpler way of using any procedure to achieve a report like this? Minor cosmetic changes can be there, preferably, closest to the layout specified.

Any help is appreciated.


Accepted Solutions
Solution
‎07-14-2013 12:22 AM
SAS Super FREQ
Posts: 8,740

Re: Create a row total in SAS

I was envisioning a one-step PROC TABULATE. See attached screen shot.

cynthia

ods listing close;

ods html file='c:\temp\income_report.html';

proc tabulate data=testdata;

  class year_mm type;

  var income1 income2;

  table year_mm all='Total',

        type*(income1 income2)*sum=' ' all='Total'*(income1 income2)*sum=' ';

run;

ods html close;


income_report.jpg

View solution in original post


All Replies
Super User
Posts: 17,750

Re: Create a row total in SAS

Proc report or Tabulate.

What's your final output destination? PDF, EXCEL, RTF?

Contributor
Posts: 50

Re: Create a row total in SAS

It can be a flat file or lst within Unix. It will not be published to any other form. I tried tabulate. It doesn work as expected..

SAS Super FREQ
Posts: 8,740

Re: Create a row total in SAS

Hi:

What did your TABULATE code look like? This looks like a TABULATE report. I don't understand how it doesn't work as expected.

cynthia

Trusted Advisor
Posts: 1,128

Re: Create a row total in SAS

Hope the below code is what you were expecting.

data income;

    input Year  Type $ Income1  Income2;

cards;

200101         A      500         200

200101         A      700         300

200201         A      600         400

200201         A      300         500

200101         B      400         600

200101         B      700          800

200201         B      600          900

200201         B       400          400

;

run;

ods output summary=summary1;

proc means data = income_ sum missing;

    class year type;

    var income1 income2;

run;

ods output close;

ods output summary=summary2(keep= year Income1_Sum Income2_Sum rename=(Income1_Sum=T_Income1_Sum Income2_Sum=T_Income2_Sum));

proc means data = income_ sum missing;

    class year ;

    var income1 income2;

run;

ods output close;

data income1(keep= year Income1_Sum Income2_Sum rename=(Income1_Sum=A_Income1_Sum Income2_Sum=A_Income2_Sum))

     income2 (keep= year Income1_Sum Income2_Sum rename=(Income1_Sum=B_Income1_Sum Income2_Sum=B_Income2_Sum));

    set summary1;

    if type='A' then output Income1;

    else if type='B' then output income2;

run;

proc sort data=income1;

    by year;

run;

proc sort data=income2;

    by year;

run;

data income3;

    merge income1 income2 summary2;

    by year;

run;

proc print data = income3;

    sum  A_Income1_Sum A_Income2_Sum B_Income1_Sum

         B_Income2_Sum t_income1_sum t_income2_sum;

run;

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 50

Re: Create a row total in SAS

Jagdish, thanks for your respone.

I had thought of that approach. But the Type column may contain any number of values. It may not contain those two values alone. I forgot to mention this in my original post. Apologies for that.

Solution
‎07-14-2013 12:22 AM
SAS Super FREQ
Posts: 8,740

Re: Create a row total in SAS

I was envisioning a one-step PROC TABULATE. See attached screen shot.

cynthia

ods listing close;

ods html file='c:\temp\income_report.html';

proc tabulate data=testdata;

  class year_mm type;

  var income1 income2;

  table year_mm all='Total',

        type*(income1 income2)*sum=' ' all='Total'*(income1 income2)*sum=' ';

run;

ods html close;


income_report.jpg
Contributor
Posts: 50

Re: Create a row total in SAS

Awesome! It worked! My try with proc tabulate was giving out data correctly but it had issues with alignment/layout. Also I was looking at a way to name All to Total.

This one is almost close to what I want to recreate! Thank you so much! Smiley Happy

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 352 views
  • 0 likes
  • 4 in conversation