Adding data in columns

[new_sas_user_4]

Hi guys.

I have a dataset like :

data Base_sample;
input dom ON QC AB Sask ;
datalines;
1 1 2 55 4
1 5 5 40 6
2 12 10 15 40
2 4 20 25 30
2 5 22 80 70
3 87 66 44 30
3 44 42 20 55
3 55 52 87 90
3 5 2 6 7
4 65 45 95 32
;

I am looking to get the sum of the ON,QC, AB, Sask in rows (Addition done based on the first column - DOM(Day of month))

My ideal result is :

        1    2     3    4       Total

ON   6   21 191  65     283   

QC   7   52  162 45     266

AB   95  120 157 95   467

Sask 10  140 182 32  364

Could you please help!

[PaigeMiller]

proc summary data=base_sample nway;
    class dom;
    var on qc ab sask;
    output out=want sum=;
run;

[PaigeMiller]

Minor fix to my code

proc summary data=base_sample; /* NWAY option removed */
    class dom;
    var on qc ab sask;
    output out=want sum=;
run;

[SuryaKiran · Accepted solution]

Hello,

Addition to @PaigeMiller solution. 

proc summary data=base_sample nway;
    class dom;
    var on qc ab sask;
    output out=sum_have sum=;
run;

proc transpose data=sum_have prefix=col_ out=trans_sum_have;
id dom;
var on qc ab sask;
run;

data want;
set trans_sum_have;
Total=sum(of col_:);
run;

[mkeintz]

I think this is a case where PROC TABULATE is the best solution.  It allows you to both get the SUM statistics you want and organize the data the way you want it, without the transpose or any other subsequent steps:

data Base_sample;
input dom ON QC AB Sask ;
datalines;
1 1 2 55 4
1 5 5 40 6
2 12 10 15 40
2 4 20 25 30
2 5 22 80 70
3 87 66 44 30
3 44 42 20 55
3 55 52 87 90
3 5 2 6 7
4 65 45 95 32
;

*ods results off;
*ods listing close;
proc tabulate data=base_sample  /*out=mytable (drop=_:)*/;
  class dom ;
  var on qc ab sask;
  tables (on qc ab sask)     /* Row specification */
          ,sum=' '*dom       /* Column specification */
  ;
run;
*ods results open;
*ods listing ;

If you want an immediate printout of the results then don't bother changing my commented statements.  But if you don't (maybe there are 1,000 values of DOM, and you don't want to print 1,000 rows), then select the output destinations currently in use in your sas sessions, and de-comment the corresponding statements before and after the proc tabulate.

[new_sas_user_4]

Thanks a lot 🙂

I do have another column which I need the data to be grouped by.

I didnt include it earlier as I thought I would be able to do it myself if I have the basic query to start off but using transpose I suppose I cant as ID would be unique values. 

Sorry to bother again!

Below is the sample data Base_sample;
length item $1;
input item $ dom On QC AB Sask ;
datalines;
A 1 1 2 55 4
B 1 5 5 40 6
A 2 12 10 15 40
A 2 4 20 25 30
B 2 5 22 80 70
A 3 87 66 44 30
A 3 44 42 20 55
B 3 55 52 87 90
C 3 5 2 6 7
C 4 65 45 95 32
;

Expected :

[PaigeMiller]

Either of these will work

• PROC SUMMARY with both ITEM and DOM in the class statement
• PROC REPORT

[new_sas_user_4]

But then how would I transpose 😕 sorry I am a noob