Help using Base SAS procedures

Conditional sum into new fields

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Conditional sum into new fields

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

|30   |40 

|60   |0 

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

1   ||R002  |20 

2   ||R000  |10 

2   ||R004  |30 

1   ||R002  |20 

1   ||R000  |40 

1   ||R004  |0 

2   ||R003  |40 

2   ||R004  |10 

1   ||R002  |20 

2   ||R002  |20


Accepted Solutions
Solution
‎05-21-2015 09:13 AM
Contributor
Posts: 44

Re: Conditional sum into new fields

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;

View solution in original post


All Replies
Solution
‎05-21-2015 09:13 AM
Contributor
Posts: 44

Re: Conditional sum into new fields

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;

New Contributor
Posts: 2

Re: Conditional sum into new fields

thanks very much

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 244 views
  • 0 likes
  • 2 in conversation