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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.