how to categorize by ID based on previous values

Reply
Occasional Contributor
Posts: 12

how to categorize by ID based on previous values

[ Edited ]

Hi everyone,

 

**POST EDITED: AUG24**

 

1) I'm using EG at my work and I was wondering if there's a simple way for me to do path analysis?

I want to understand the path (channel) customers take to get to their first channel = 3 transaction. I only want to look at transactions with Rev>=0, and exclude customers whose first transaction is channel = 3 i.e. ID 2

ie. ID 1 => 1 -> 3, PATH = '1 only'; ID 6=> 2->1->2->1->3, PATH = '1 & 2'.

 

 Data set: HAVE

IDTRANS_DATETRANS_KEYUNITSREVCHANNEL
111JUL2015106821939.981
114JUL20151256131.991
117JUL20159305263.981
120JUL2015120722279.981
120JUL20151477-2-1939.981
108SEP2015511321539.991
124FEB201688351299.993
201SEP201551781139.993
228SEP201523681214.991
330JUL201515041189.953
422DEC20156272819.983
402JAN20161271-2-819.981
512AUG201591402649.983
512AUG20153423-1-199.991
512AUG201534242545.931
627DEC20150947239.982
625JAN201621101119.991
630JAN201632121149.992
630JAN201633201149.992
602FEB201631560-112
604FEB20162395-1-149.991
604FEB20162399-1-137.561
604FEB2016431199.993
705MAR2016842631502.973
811AUG201546891469.993
811AUG20158172114.951
918SEP201512432477.581
926SEP20155399-2-477.581
926SEP201554912359.991
926DEC20155521699.991
927DEC20155969-1-699.991
927DEC201510181999.991
903JAN20166506-1-24.991
904JAN20168623-1-999.991
910JAN2016609716101
916JAN20162121829.993
927JAN20166767139.992
1013MAR201668921149.992
1008MAY201659011109.993
1008MAY201612437.971
1008MAY2016368341849.991
1009MAY2016596311601
1130AUG201579271369.993
1130AUG201543700801

 

 

Data set: WANT

i want a variable PATH with 3 categories: 1 only, 2 only, 1 & 2

ID

PATH

11 only
61 & 2 
9

1 only

10

2 only

  

 

 

2) i also have a side question....Is there a simple way to delete all ID rows where first transaction is channel =3? 

 

 

Thanks for your help, much appreciated.

SAS Super FREQ
Posts: 3,618

Re: Doing path analysis in EG/SAS Foundation?

Have you already read this article on "How to apply Path Analysis in EG"?

 

 

Occasional Contributor
Posts: 12

Re: Doing path analysis in EG/SAS Foundation?

Hi,

The article shows steps for doing path analysis in SAS EM, not EG. Unfortunately my EM isn't working at the moment...

Super User
Posts: 9,856

Re: Doing path analysis in EG/SAS Foundation?

Here is for your second question.



data have;
infile cards expandtabs truncover;
input ID	TRANS_DATE : $20.	TRANS_KEY	UNITS	REV	CHANNEL;
cards;
1	11JUL2015	1068	2	1939.98	1
1	14JUL2015	1256	1	31.99	1
1	17JUL2015	9305	2	63.98	1
1	20JUL2015	1207	2	2279.98	1
1	20JUL2015	1477	-2	-1939.98	1
1	08SEP2015	5113	2	1539.99	1
1	24FEB2016	8835	1	299.99	3
2	01SEP2015	5178	1	139.99	3
2	28SEP2015	2368	1	214.99	1
3	30JUL2015	1504	1	189.95	3
4	22DEC2015	627	2	819.98	3
4	02JAN2016	1271	-2	-819.98	1
5	12AUG2015	9140	2	649.98	3
5	12AUG2015	3423	-1	-199.99	1
5	12AUG2015	3424	2	545.93	1
6	27DEC2015	0947	2	39.98	2
6	25JAN2016	2110	1	119.99	1
6	30JAN2016	3212	1	149.99	2
6	30JAN2016	3320	1	149.99	2
6	02FEB2016	3156	0	-11	2
6	04FEB2016	2395	-1	-149.99	1
6	04FEB2016	2399	-1	-137.56	1
6	04FEB2016	431	1	99.99	3
7	05MAR2016	8426	3	1502.97	3
8	11AUG2015	4689	1	469.99	3
8	11AUG2015	8172	1	14.95	1
9	18SEP2015	1243	2	477.58	1
9	26SEP2015	5399	-2	-477.58	1
9	26SEP2015	5491	2	359.99	1
9	26DEC2015	552	1	699.99	1
9	27DEC2015	5969	-1	-699.99	1
9	27DEC2015	1018	1	999.99	1
9	03JAN2016	6506	-1	-24.99	1
9	04JAN2016	8623	-1	-999.99	1
9	10JAN2016	6097	1	610	1
9	16JAN2016	212	1	829.99	3
9	27JAN2016	6767	1	39.99	2
10	13MAR2016	6892	1	149.99	1
10	08MAY2016	5901	1	109.99	3
10	08MAY2016	124	3	7.97	1
10	08MAY2016	3683	4	1849.99	1
10	09MAY2016	5963	1	160	1
11	30AUG2015	7927	1	369.99	3
11	30AUG2015	4370	0	80	1
;
run;
data want;
 do until(last.id);
  set have;
  by id;
  if first.id and CHANNEL=3 then flag=1;
 end;
 do until(last.id);
  set have;
  by id;
  if not flag then output;
 end;
 drop flag;
run;


Occasional Contributor
Posts: 12

Re: Doing path analysis in EG/SAS Foundation?

Thanks Xia, the program worked for me.

I have one follow-up question...do you know how i can delete rows after the first time channel=3 comes up?

or is there a way for me to flag all the rows up to and including the first channel=3 line? not sure which one is easier...

 

thanks for your help.

 

i.e. HAVE

IDTRANS_DATETRANS_KEYCHANNEL
1229FEB201691652
1207MAR201654142
1229APR201699772
1230APR201646423
1230APR201670721
1230APR201647111
1230APR201655783
1230APR201655791

 

data WANT:

IDTRANS_DATETRANS_KEYCHANNEL
1229FEB201691652
1207MAR201654142
1229APR201699772
1230APR201646423

 

Super User
Posts: 9,856

Re: Doing path analysis in EG/SAS Foundation?

data have;
infile cards expandtabs truncover;
input ID	TRANS_DATE : $20.	TRANS_KEY	UNITS	REV	CHANNEL;
cards;
1	11JUL2015	1068	2	1939.98	1
1	14JUL2015	1256	1	31.99	1
1	17JUL2015	9305	2	63.98	1
1	20JUL2015	1207	2	2279.98	1
1	20JUL2015	1477	-2	-1939.98	1
1	08SEP2015	5113	2	1539.99	1
1	24FEB2016	8835	1	299.99	3
2	01SEP2015	5178	1	139.99	3
2	28SEP2015	2368	1	214.99	1
3	30JUL2015	1504	1	189.95	3
4	22DEC2015	627	2	819.98	3
4	02JAN2016	1271	-2	-819.98	1
5	12AUG2015	9140	2	649.98	3
5	12AUG2015	3423	-1	-199.99	1
5	12AUG2015	3424	2	545.93	1
6	27DEC2015	0947	2	39.98	2
6	25JAN2016	2110	1	119.99	1
6	30JAN2016	3212	1	149.99	2
6	30JAN2016	3320	1	149.99	2
6	02FEB2016	3156	0	-11	2
6	04FEB2016	2395	-1	-149.99	1
6	04FEB2016	2399	-1	-137.56	1
6	04FEB2016	431	1	99.99	3
7	05MAR2016	8426	3	1502.97	3
8	11AUG2015	4689	1	469.99	3
8	11AUG2015	8172	1	14.95	1
9	18SEP2015	1243	2	477.58	1
9	26SEP2015	5399	-2	-477.58	1
9	26SEP2015	5491	2	359.99	1
9	26DEC2015	552	1	699.99	1
9	27DEC2015	5969	-1	-699.99	1
9	27DEC2015	1018	1	999.99	1
9	03JAN2016	6506	-1	-24.99	1
9	04JAN2016	8623	-1	-999.99	1
9	10JAN2016	6097	1	610	1
9	16JAN2016	212	1	829.99	3
9	27JAN2016	6767	1	39.99	2
10	13MAR2016	6892	1	149.99	1
10	08MAY2016	5901	1	109.99	3
10	08MAY2016	124	3	7.97	1
10	08MAY2016	3683	4	1849.99	1
10	09MAY2016	5963	1	160	1
11	30AUG2015	7927	1	369.99	3
11	30AUG2015	4370	0	80	1
;
run;
data want;
 do i=1 by 1 until(last.id);
  set have;
  by id;
  if not flag and CHANNEL=3 then do;flag=1;k=i;end;
 end;
 do j=1 by 1 until(last.id);
  set have;
  by id;
  if j le k then output;
 end;
 drop flag i j k;
run;


Ask a Question
Discussion stats
  • 5 replies
  • 345 views
  • 0 likes
  • 3 in conversation