topic Re: How to create customer flow table based on daily snap shot in SAS Programming

How to create customer flow table based on daily snap shot

ycubic — Tue, 17 Mar 2015 23:35:00 GMT

I have a data set as below.

country code	snapshot date
canada	1/3/2014
canada	1/4/2014
us	1/5/2014
us	1/6/2014
us	1/7/2014
canada	1/8/2014
canada	1/9/2014

I would like to create a result table as below.

country code	start snapshot date	end snapshot date
canada	1/3/2014	1/4/2014
us	1/5/2014	1/7/2014
canada	1/8/2014	1/9/2014

I can only create the start snapshot date by rank and join the same table, and am having trouble to get the end snapshot date.

Re: How to create customer flow table based on daily snap shot

Ksharp — Wed, 18 Mar 2015 12:20:24 GMT

data have;
input country $      date : mmddyy10.;
format date mmddyy10.;
cards;
canada     1/3/2014
canada     1/4/2014
us     1/5/2014
us     1/6/2014
us     1/7/2014
canada     1/8/2014
canada     1/9/2014
;
run;
data want;
 set have;
 by country notsorted;
 retain start;
 if first.country then start=date;
 if last.country then do; end=date;output;end;
 drop date;
 format start end mmddyy10.;
run;

Xia Keshan

Re: How to create customer flow table based on daily snap shot

ycubic — Thu, 19 Mar 2015 11:21:13 GMT

thank you. i didn't think it this way, the sas way. i think i was too much into the sql way.

Re: How to create customer flow table based on daily snap shot

Reeza — Thu, 19 Mar 2015 11:46:27 GMT

The SQL way

proc sql;

create table daily_snapshot as

select country_code, min(date) as start_date, max(date) as end_date

from have

group by country_code

order by country_code;

quit;