BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adi121
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

3 REPLIES 3
adi121
Fluorite | Level 6

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

adi121
Fluorite | Level 6

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

PGStats
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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