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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.