Hello All
I am attempting to do a proc transpose and I am a beginner with programming SAS. With the dataset I currently have I am not able to do it and then wrote a 200 line code to achieve it.
Here is the code. Could anyone help me do the same job with more efficient code using the proc transpose statement.
Thanks for your help in advance.
_____________________________________________________________
proc sort data=Datadump.Raw_jumbo_data out=Datadump.temp1;
by org_id;
run;
proc freq data=Datadump.temp1;
tables activity;
run;
proc sort data=datadump.temp1 out=datadump.temp1a;
by org_id workarea;
run;
proc transpose data=Datadump.temp1a out=Datadump.temp2;
by org_id Workarea;
id activity ;
var Waterfall;
run;
data Datadump.temp3 ;
set Datadump.temp2;
by org_id;
if first.org_id then do;
stayplat = 1;
end;
run;
data Datadump.temp4 (drop=stayplat Safe_dep night_bag ATM_Env
ATM_svc Trans);
set Datadump.temp3;
if stayplat=. then delete;
run;
proc contents data=Datadump.temp2;
run;
proc contents data=Datadump.temp3;
run;
proc contents data=datadump.temp4;
run;
data Datadump.tempWF (drop=_NAME_ _LABEL_ Onboarding
Consumer_Proactive Service Turn_train Ops
Abs Mgmt);
set Datadump.temp4;
rename Business_Proactive = WF_Business_Proactive;
rename Investment_Sales___Fulfillment = WF_Investment_Sales_Fulfillment;
rename Sales_Fulfillment = WF_Sales_Fulfillment;
rename Work_Perks = WF_Work_Perks;
run;
proc contents data=datadump.tempWF;
run;
proc transpose data=Datadump.temp1a out=Datadump.temp5;
by org_id workarea;
id activity;
var hours;
run;
data Datadump.temp6 ;
set Datadump.temp5;
by org_id;
if first.org_id then do;
stayplat = 1;
end;
run;
data Datadump.temp7 (drop=stayplat Safe_dep night_bag ATM_Env
ATM_svc Trans);
set Datadump.temp6;
if stayplat=. then delete;
run;
proc contents data=datadump.temp7;
run;
data Datadump.tempHR (drop=_NAME_ _LABEL_ Consumer_Proactive Onboarding
Service Turn_train Ops
Abs Mgmt);
set Datadump.temp7;
rename Business_Proactive = HR_Business_Proactive;
rename Investment_Sales___Fulfillment = HR_Investment_Sales_Fulfillment;
rename Sales_Fulfillment = HR_Sales_Fulfillment;
rename Work_Perks = HR_Work_Perks;
run;
proc contents data=datadump.temp7;
run;
data Datadump.temp1inter2 (drop=Workarea Activity Waterfall Hours
Total_Consumer_Deposits Total_Business_Deposits
Consumer_Loan_Lines Business_Loan_Lines);
set Datadump.temp1;
by org_id;
if first.org_id then do;
stay=1;
end;
run;
proc contents data=datadump.temp1inter2;
run;
data Datadump.tempbrch (drop=stay);
set Datadump.temp1inter2;
if stay=. then delete;
run;
data Datadump.temp1inter3 (drop=Workarea Activity Waterfall Hours
Denovo_09 Instore_ CRA_ MAB_
Facilities_Segment);
set Datadump.temp1;
by org_id;
if first.org_id then do;
stay=1;
end;
run;
proc contents data=datadump.temp1inter3;
data Datadump.tempDep (drop= Market District Branch stay);
set Datadump.temp1inter3;
if stay=. then delete;
run;
proc contents data=datadump.tempDep;
run;
data Datadump.temp8 (drop=stayplat Business_Proactive Investment_Sales__Fulfillment
Sales_Fulfillment Work_perks);
set Datadump.temp3;
if stayplat=. then delete;
run;
data datadump.WFplatother (keep = org_id WF_Tot_Other_Platform_Act);
set datadump.temp8;
WF_Tot_Other_Platform_Act = sum(Turn_train, ops, abs, mgmt,
consumer_proactive, Onboarding, service);
format WF_Tot_Other_Platform_Act 3.2;
run;
data Datadump.temp9 (drop=stayplat Business_Proactive Investment_Sales__Fulfillment
Sales_Fulfillment Work_perks);
set Datadump.temp6;
if stayplat=. then delete;
run;
data datadump.HRplatother (keep = org_id HR_Tot_Other_Platform_Act);
set datadump.temp9;
HR_Tot_Other_Platform_Act = sum(Turn_train, ops, abs, mgmt,
consumer_proactive, Onboarding, service);
format HR_Tot_Other_Platform_Act 3.2;
run;
data Datadump.temp10 ;
set Datadump.temp2;
by org_id;
if last.org_id then do;
staytel = 1;
end;
run;
data Datadump.temp11 (drop=staytel Business_Proactive
Investment_Sales_Fulfillment
Sales_Fulfillment Work_perks
Consumer_Proactive Onboarding Service);
set Datadump.temp10;
if staytel=. then delete;
run;
data datadump.WFtelother (keep = org_id WF_Tot_Other_Teller_Act);
set datadump.temp11;
WF_Tot_Other_Teller_Act = sum(Abs, ops, mgmt, turn_train,trans,safe_dep,
night_bag, ATM_env, ATM_svc);
format WF_Tot_Other_Teller_Act 3.2;
run;
data Datadump.temp12 ;
set Datadump.temp5;
by org_id;
if last.org_id then do;
staytel = 1;
end;
run;
data Datadump.temp13 (drop=staytel Business_Proactive
Investment_Sales___Fulfillment
Sales_Fulfillment Work_perks
Consumer_Proactive Onboarding Service);
set Datadump.temp12;
if staytel=. then delete;
run;
data datadump.HRtelother (keep = org_id HR_Tot_Other_Teller_Act);
set datadump.temp13;
HR_Tot_Other_Teller_Act = sum(Abs, ops, mgmt, turn_train,trans,safe_dep,
night_bag, ATM_env, ATM_svc);
format HR_Tot_Other_Teller_Act 3.2;
run;
______________________________________________________________