BookmarkSubscribeRSS Feed
lai302120
Calcite | Level 5

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.

5 REPLIES 5
Rick_SAS
SAS Super FREQ

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

 

 

lai302120
Calcite | Level 5

Hi,

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

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


lai302120
Calcite | Level 5

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

 

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


sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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