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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.