transpose

Reply
Contributor
Posts: 44

transpose

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 

 

Respected Advisor
Posts: 4,654

Re: transpose

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
Super User
Posts: 9,687

Re: transpose


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;

Super User
Posts: 9,687

Re: transpose

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;

Super User
Posts: 9,687

Re: transpose

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;

Contributor
Posts: 44

Re: transpose

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
Respected Advisor
Posts: 4,654

Re: transpose

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

PG
Contributor
Posts: 44

Re: transpose

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.
Respected Advisor
Posts: 4,654

Re: transpose

So, how do you tell which codes go together?

PG
Contributor
Posts: 44

Re: transpose

[ Edited ]

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.

Super User
Posts: 9,687

Re: transpose

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;

Contributor
Posts: 44

Re: transpose

Do i have to create a macro to pass the value in to ID
Super User
Posts: 9,687

Re: transpose

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;


Ask a Question
Discussion stats
  • 12 replies
  • 684 views
  • 0 likes
  • 3 in conversation