sas conditional sum into new field

Question

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 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

Answer 1

You can solve the problem with a simple data step, see the code below. You need to sort the data first, and then instruct the data to work with groups where you reset the day_1 and day_2 to zero at the start of a new group and you output to the data set at the last observation.

Let me know if you have any questions.

data ORIG_DATA  ;
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
;run;

proc sort;
   by salesman_id; 
RUN; 

data salesman_id (drop=Day_ID Other_field total); 
  set orig_data; 
  by salesman_id; 
   if first.salesman_id then do; 
     day_1 = 0; 
     day_2 = 0;
   end; 
  if day_id=1 then day_1 + total; 
  if day_id=2 then day_2 + total;
  if last.salesman_id then output; 
RUN;