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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: