topic Re: Transpose using proc sql in SAS Programming

Transpose using proc sql

adi121 — Mon, 21 May 2018 04:12:45 GMT

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

Re: Transpose using proc sql

adi121 — Mon, 21 May 2018 04:26:47 GMT

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

Re: Transpose using proc sql

adi121 — Mon, 21 May 2018 04:27:55 GMT

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

Re: Transpose using proc sql

PGStats — Mon, 21 May 2018 06:02:06 GMT

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;