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
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;
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
im missing out the month names ,how to include them in output????
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.