Sas notional amount to new field
I am new to SAS and have a simple ORIG_DATA dataset from which I need to create a new SUMMARY dataset that shows the total Salesman_ID by Day_ID
Basically, the SUMMARY output should look like this, where the numbers are the sum of the totals.
Salesman_ID|Day_1|Day_2
A |30 |40
B |60 |0
C |20 |70
In SQL, I am doing
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 am not allowed to use proc sql. How else can I do this in SAS? Not the dumbest at the moment. apologies for the non-standard format
ORIG_DATA as shown 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
source to share
Similar:
proc sort data = orig_data(drop = Other_field);
by salesman_id day_id;
run;
data test (drop = total);
retain salesman_id day_id;
set orig_data ;
by salesman_id day_id notsorted;
if first.day_id then sum = total;
else sum + total;
if last.day_id then output;
run;
proc transpose data = test out = t(drop=_:) prefix = day_id_;
by salesman_id;
id day_id;
var sum;
run;
source to share
You can solve the problem with a simple data step, see the code below. You first need to sort the data first and then instruct the data to work with groups where you reset day_1 and day_2 to zero at the start of a new group and you exit to the dataset on 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;
source to share
How about this? I do not know whether you have only two entries other_field
for salesman_id
for day_id
. Below 1 to n entries will work:
Input data:
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;
Transpose, sum and carry back:
proc sort data=ORIG_DATA ;
by salesman_id day_id ;
proc transpose data=ORIG_DATA out=D1 ;
by salesman_id day_id ;
var total ;
run ;
data D2 ;
set D1 ;
array D(*) col: ;
_name_=cats('day_',day_id) ;
by salesman_id day_id;
total=sum(of D(*)) ;
run ;
proc transpose data=D2 out=SUMMARY(drop=_name_) name=_name_;
by salesman_id ;
var total ;
run ;
*Add zeros for missing values ;
data SUMMARY ;
set SUMMARY ;
array days day_: ;
do over days ;
if missing(days) then days=0;
end ;
run ;
source to share