transpose

[boin]

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 

[PGStats]

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;

[Ksharp]

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]

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]

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]

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]

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

[boin]

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]

So, how do you tell which codes go together?

[boin]

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]

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]

Do i have to create a macro to pass the value in to ID

[Ksharp]

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;