topic Re: Adding data in columns in SAS Programming

Adding data in columns

new_sas_user_4 — Mon, 25 Nov 2019 18:55:47 GMT

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!

Re: Adding data in columns

PaigeMiller — Mon, 25 Nov 2019 19:00:39 GMT

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

Re: Adding data in columns

SuryaKiran — Mon, 25 Nov 2019 19:14:11 GMT

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;

Re: Adding data in columns

PaigeMiller — Mon, 25 Nov 2019 19:28:12 GMT

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;

Re: Adding data in columns

mkeintz — Mon, 25 Nov 2019 19:43:24 GMT

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.

Re: Adding data in columns

new_sas_user_4 — Mon, 25 Nov 2019 20:39:50 GMT

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 :

Re: Adding data in columns

PaigeMiller — Mon, 25 Nov 2019 20:51:05 GMT

Either of these will work

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

Re: Adding data in columns

new_sas_user_4 — Mon, 25 Nov 2019 21:44:33 GMT

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