DATA Step, Macro, Functions and more

Transpose using proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Transpose using proc sql

i have 2 dataset and have merged it

 

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;

 

proc sql;
create table race2 as
select cust_id, sum(spend) as JAN, sum(spend) as feb,sum(spend) as Mar
from ccs2 where quarter ="Q1"
group by cust_id;
quit;

 

im able to transpose but im not getting correct results

 

i want output like

CUST_ID

JAN

FEB

MAR

APR

JUN

1

SPEND IN JAN

SPEND IN FEB

SPEND IN MAR

SPEND IN APR

SPEND IN JUN

2

…………..

….

….

….

 

 

 

 

 

 

 

what am i doing wrong


Accepted Solutions
Solution
‎05-27-2018 02:46 PM
Esteemed Advisor
Posts: 5,539

Re: Transpose using proc sql

I think you want:

 

proc sql;
create table race3 as
select 
    cust_id,
    sum(case when Month = "JAN" then spend else . end) as JAN,
    sum(case when Month = "FEB" then spend else . end) as FEB,
    sum(case when Month = "MAR" then spend else . end) as MAR
from ccs2 
where quarter = "Q1" 
group by cust_id;
quit;
PG

View solution in original post


All Replies
Occasional Contributor
Posts: 15

Re: Transpose using proc sql

proc sql noprint;
create table race3 as
(select cust_id,
max(case when Month = "JAN" then spend else . end) as Spend_Jan,
max(case when Month = "FEB" then spend else . end) as Spend_FEB,
max(case when Month = "MAR" then spend else . end) as Spend_MAR
from ccs2 where quarter="Q1" group by cust_id);
quit;

 

this is correct but not exaclt what i want

Occasional Contributor
Posts: 15

Re: Transpose using proc sql

im missing out the month names ,how to include them in output????

Solution
‎05-27-2018 02:46 PM
Esteemed Advisor
Posts: 5,539

Re: Transpose using proc sql

I think you want:

 

proc sql;
create table race3 as
select 
    cust_id,
    sum(case when Month = "JAN" then spend else . end) as JAN,
    sum(case when Month = "FEB" then spend else . end) as FEB,
    sum(case when Month = "MAR" then spend else . end) as MAR
from ccs2 
where quarter = "Q1" 
group by cust_id;
quit;
PG
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 135 views
  • 1 like
  • 2 in conversation