topic Re: Conditional sum into new fields in SAS Procedures

Conditional sum into new fields

wanderingmanifold — Thu, 21 May 2015 10:04:17 GMT

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

Re: Conditional sum into new fields

AskoLötjönen — Thu, 21 May 2015 13:13:31 GMT

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;

Re: Conditional sum into new fields

wanderingmanifold — Sun, 24 May 2015 15:16:17 GMT

thanks very much