Transpose data using data set approach

adi121 — Mon, 21 May 2018 20:00:25 GMT

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

Re: Transpose data using data set approach

art297 — Mon, 21 May 2018 20:12:43 GMT

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

topic Transpose data using data set approach in SAS Programming

Transpose data using data set approach

Re: Transpose data using data set approach