BookmarkSubscribeRSS Feed
boin
Obsidian | Level 7

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 

 

12 REPLIES 12
PGStats
Opal | Level 21

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;
PG
Ksharp
Super User

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;

Ksharp
Super User
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;

Ksharp
Super User
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;

boin
Obsidian | Level 7

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
PGStats
Opal | Level 21

Watch the 9458277 typo. My "idea" up there will do it.

PG
boin
Obsidian | Level 7
Thanks PG, Yes I looked at your response.It is dependent on number of emails sent in a month.In some instances the set (3000,3006,4001) or (3001,3006,4001) can spread across multiple months.All the three sends might not occur in a month.
PGStats
Opal | Level 21

So, how do you tell which codes go together?

PG
boin
Obsidian | Level 7

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.

Ksharp
Super User
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;

boin
Obsidian | Level 7
Do i have to create a macro to pass the value in to ID
Ksharp
Super User
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;


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
  • 12 replies
  • 1736 views
  • 0 likes
  • 3 in conversation