Help with Proc Transpose

deleted_user — Fri, 09 Jan 2009 20:12:09 GMT

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

Re: Help with Proc Transpose

sbb — Sat, 10 Jan 2009 03:02:28 GMT

I would suggest you tell the forum subscribers what it is you are trying to accomplish in words, and also provide some INPUT and OUTPUT (desired results after TRANSPOSE) in order to get some useful feedback.

Scott Barry
SBBWorks, Inc.

Re: Help with Proc Transpose

jf — Tue, 13 Jan 2009 21:06:46 GMT

If you can post your original data set ( a few lines) and desired data format, I can try it.

Re: Help with Proc Transpose

deleted_user — Wed, 14 Jan 2009 19:57:34 GMT

Hello All

Here are the formats of the original dataset, the explaination of my code and the output dataset.

Input Dataset: I have the data arranged in the following format for many markets including chicago.

Org ID Market Branch Work Area Activity Waterfall Hours
1111 Chicago B1 Platform Sales Ful 0.32 3.2
1111 Chicago B1 Platform bus-proc 0.85 2.1
1111 Chicago B1 Platform inv-sal-ful 0.26 1.1
1111 Chicago B1 Platform cons-proc 0.41 2
1111 Chicago B1 Platform work perks 0.55 12
1111 Chicago B1 teller trans 0.6 6
1111 Chicago B1 teller night-bag 0.22 0.2
1111 Chicago B1 teller service 0.11 5

Output dataset

For output dataset I wish to have the data for 1 maket in 1 line. So the headers for the columns would be:
Org_ID, Market,Branch Name,Waterfall - Sales Ful, Waterfall - bus proc, Waterfall - inv-sal-ful, Waterfall - cons proc, waterfall - works perks, waterfall - nightbag, waterfall service, Hours - Sales Ful, Hours - bus proc, Hours - inv-sal-ful, Hours - cons proc, Hours - works perks, Hours - nightbag, Hours - service

The code mentioned below includes many markets and many other variables. Could anyone help me write the code for the above mentioned variables using Proc Transpose.

Once again thanks for all help in advance.
Pappu

topic Help with Proc Transpose in SAS Procedures

Help with Proc Transpose

Re: Help with Proc Transpose

Re: Help with Proc Transpose

Re: Help with Proc Transpose