Hi Team,
I have the below table :
membership | Email_id | Date |
9458247 | 3000 | 1/08/2016 |
9458247 | 3006 | 12/08/2016 |
9458247 | 4001 | 18/08/2016 |
9458247 | 3000 | 2/09/2016 |
9458247 | 3006 | 14/09/2016 |
and i am trying for the below output:
membership | 3000 | 3006 | 4001 | Max(date) |
9458247 | Y | Y | Y | 18/08/2016 |
9458247 | Y | Y | 14/09/2016 |
A customer could receive three messages (one set 300,3006,4001) on a marketing campaign ,if the customer is eligible again he will receive another set of three messages next month.
For each set i want to capture the max(date) , which is the last message date on that set.
any ideas would be great ..
Thanks
An idea:
proc sql;
create table temp as
select *, "Y" as dum,
intnx("MONTH", date, 0) as month format=yymmd7.,
max(date) as lastDate format=ddmmyy10.
from have
group by membership, calculated month
order by membership, month, email_id;
quit;
proc transpose data=temp out=want(drop=_name_);
by membership month lastDate;
var dum;
id email_id;
idlabel email_id;
run;
proc print data=want noobs label; run;
data have; infile cards expandtabs truncover; input membership Email_id Date : ddmmyy10.; format date ddmmyy10.; cards; 9458247 3000 1/08/2016 9458247 3006 12/08/2016 9458247 4001 18/08/2016 9458247 3000 2/09/2016 9458247 3006 14/09/2016 ; run; data temp; if _n_=1 then do; if 0 then set have; declare hash h(); h.definekey('Email_id'); h.definedone(); end; set have; retain v 'Y'; if h.check() ne 0 then h.add(); else do; n+1;h.clear(); end; run; proc sql; create table temp1 as select *,max(date) as max_date format=ddmmyy10. from temp group by n; quit; proc transpose data=temp1 out=want; by membership max_date notsorted; id Email_id; var v; run;
Opps. The last proc transpose should be : proc transpose data=temp1 out=want; by n membership max_date notsorted; id Email_id; var v; run;
If you have multiple membership . data have; infile cards expandtabs truncover; input membership Email_id Date : ddmmyy10.; format date ddmmyy10.; cards; 9458247 3000 1/08/2016 9458247 3006 12/08/2016 9458247 4001 18/08/2016 9458247 3000 2/09/2016 9458247 3006 14/09/2016 9458277 4001 2/09/2016 ; run; data temp; if _n_=1 then do; if 0 then set have; declare hash h(); h.definekey('Email_id'); h.definedone(); end; set have; by membership; retain v 'Y'; if first.membership then do;n+1;h.clear();end; if h.check() ne 0 then h.add(); else do; n+1;h.clear(); end; run; proc sql; create table temp1 as select *,max(date) as max_date format=ddmmyy10. from temp group by n; quit; proc transpose data=temp1 out=want; by n membership max_date notsorted; id Email_id; var v; run;
Thanks Ksharp for the solution .
Is it possible to create a set for email_id (3000,3006,4001) and another set (3001,3006,4001)
9458247 | 3000 | 1/08/2016 |
9458247 | 3006 | 12/08/2016 |
9458247 | 4001 | 18/08/2016 |
9458247 | 3001 | 2/09/2016 |
9458247 | 3006 | 14/09/2016 |
9458277 | 4001 | 2/09/2016 |
and the output as :
3000 | 3006 | 4001 | 3001 | Max(date) | |
9458247 | Y | Y | Y | 18/08/2016 | |
9458247 | Y | Y | Y | 2/09/2016 |
Watch the 9458277 typo. My "idea" up there will do it.
So, how do you tell which codes go together?
The marketing campaign has a sequence of emails .
(3000,3006,4001) is one set of sequence and
(3001,3006,4001) is another set of sequence .
Basically the sets are fixed.
data have; id=9458247; do year=2015 to 2016; do month=1 to 12; date=mdy(month,1,year); do email=3000,3006,4001; output; end; end; end; drop year month; format date date9.; run; data want; set have; retain group -1 v 'Y'; if email=3000 then do; group+1; email=email+group; end; run;
No. You don't need. Make a BY variable. data have; do id=9458247,9459346; do year=2015 to 2016; do month=1 to 12; date=mdy(month,1,year); do email=3000,3006,4001; output; end; end; end; end; drop year month; format date date9.; run; data want; set have; by id; retain group -1 v 'Y'; if first.id then group=-1; if email=3000 then do; group+1; email=email+group; end; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.