How to create customer flow table based on daily snap shot

Reply
New Contributor
Posts: 2

How to create customer flow table based on daily snap shot

I have a data set as below.

country codesnapshot date
canada1/3/2014
canada1/4/2014
us1/5/2014
us1/6/2014
us1/7/2014
canada1/8/2014
canada1/9/2014


I would like to create a result table as below.

country codestart snapshot dateend snapshot date
canada1/3/20141/4/2014
us1/5/20141/7/2014
canada1/8/20141/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.

Super User
Posts: 9,854

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

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

New Contributor
Posts: 2

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

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

Super User
Posts: 18,997

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

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;

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