DATA Step, Macro, Functions and more

Transpose data using data set approach

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Transpose data using data set approach


data Customers;
input Cust_id Month Spend;
cards;
1 JAN 100
1 FEB 200
1 MAR 300
1 APR
2 JAN 400
2 FEB 100
2 MAR 600
3 JUN 100
;run;


data CUSTOMER_SEGMENTAION;
input CUST_id Segment;
cards;
1 HIGH
2 MOD
3 LOW
4 HIGH
5 MOD
6 LOW
;run;

 

data ccs2;
merge customers(in=x) CUSTOMER_SEGMENTAION(in=y);
by cust_id;
if x and y;
if Month in("JAN" "FEB" "MAR") then Quarter="Q1";
if Month in("APR" "MAY" "JUN") then Quarter="Q2";
run;

 


data race5;
set ccs2;
by cust_id;
array allsp(3) s1-s3;
if first.cust_id then i=1;
allsp(i)=spend;
if last.cust_id then output;
i+1;
retain s1-s3;
where Quarter="Q1";
drop Segment Quarter Spend Month i;
run; 

 

i have merged two datasets and transposed using base sas method.

im getting results also correct but  i want to rename variable like JAN ,Feb,Mar instead of s1,s2,s3

 

im getting output as:

 

CUST_ID

s1

s1

s3

  

1

100

200

300

 

 

2

…………..

….

….

  

 

kindly tell how to rename in transpose step


Accepted Solutions
Solution
‎05-23-2018 12:35 AM
PROC Star
Posts: 8,151

Re: Transpose data using data set approach

You're getting s1-s3 because those are the names you declared in your array statement. Did you want?:

 

data race5;
  set ccs2;
  by cust_id;
  array allsp(3) jan feb mar;
  if first.cust_id then i=1;
  allsp(i)=spend;
  if last.cust_id then output;
  i+1;
  retain jan feb mar;
  where Quarter="Q1";
  drop Segment Quarter Spend Month i;
run; 

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎05-23-2018 12:35 AM
PROC Star
Posts: 8,151

Re: Transpose data using data set approach

You're getting s1-s3 because those are the names you declared in your array statement. Did you want?:

 

data race5;
  set ccs2;
  by cust_id;
  array allsp(3) jan feb mar;
  if first.cust_id then i=1;
  allsp(i)=spend;
  if last.cust_id then output;
  i+1;
  retain jan feb mar;
  where Quarter="Q1";
  drop Segment Quarter Spend Month i;
run; 

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 85 views
  • 0 likes
  • 2 in conversation