I'm new to SAS and have a simple dataset called ORIG_DATA from which I need to create a new dataset SUMMARY which shows the total by Salesman_ID by Day_ID
In essence the SUMMARY output should look like this where the numbers are the sum of totals.
Salesman_ID|Day_1|Day_2
A |30 |40
B |60 |0
C |20 |70
In SQL, I'd do
Select salesman_id, sum(case when day_id=1 then total else 0 end) as day_1, sum(case when day_id=2 then total else 0 end) as day_2 from ORIG_DATA group by salesman_id
but for this problem I'm not allowed to use proc sql. How else can I do this in SAS? Haven't the foggiest at the moment. apologies for nontable format
ORIG_DATA as below
Day_ID|Salesman_ID|Other_field|total
1 |A |R000 |10
1 |A |R002 |20
2 |A |R000 |10
2 |A |R004 |30
1 |B |R002 |20
1 |B |R000 |40
1 |B |R004 |0
2 |C |R003 |40
2 |C |R004 |10
1 |C |R002 |20
2 |C |R002 |20
data have;
input Day_ID Salesman_ID $ Other_field $ total ;
cards;
1 A R000 10
1 A R002 20
2 A R000 10
2 A R004 30
1 B R002 20
1 B R000 40
1 B R004 0
2 C R003 40
2 C R004 10
1 C R002 20
2 C R002 20
;
proc SUMMARY sum DATA=HAVE nway;
CLASS Salesman_ID Day_ID;
VAR total;
OUTPUT sum=total OUT=TMP1(DROP=_freq_ _type);
RUN;
PROC TRANSPOSE DATA=TMP1 OUT=SUMMARY(DROP=_NAME_) PREFIX=day_;
VAR TOTAL;
ID day_id;
BY Salesman_ID;
RUN;
data have;
input Day_ID Salesman_ID $ Other_field $ total ;
cards;
1 A R000 10
1 A R002 20
2 A R000 10
2 A R004 30
1 B R002 20
1 B R000 40
1 B R004 0
2 C R003 40
2 C R004 10
1 C R002 20
2 C R002 20
;
proc SUMMARY sum DATA=HAVE nway;
CLASS Salesman_ID Day_ID;
VAR total;
OUTPUT sum=total OUT=TMP1(DROP=_freq_ _type);
RUN;
PROC TRANSPOSE DATA=TMP1 OUT=SUMMARY(DROP=_NAME_) PREFIX=day_;
VAR TOTAL;
ID day_id;
BY Salesman_ID;
RUN;
thanks very much
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.