BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
______________________________________________________________
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
jf
Fluorite | Level 6 jf
Fluorite | Level 6
If you can post your original data set ( a few lines) and desired data format, I can try it.
deleted_user
Not applicable
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 613 views
  • 0 likes
  • 3 in conversation