- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Suryakiran
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc summary data=base_sample nway;
class dom;
var on qc ab sask;
output out=want sum=;
run;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Suryakiran
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set
Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets
--------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 :
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Either of these will work
- PROC SUMMARY with both ITEM and DOM in the class statement
- PROC REPORT
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
But then how would I transpose 😕 sorry I am a noob