topic Re: count recent matches in SAS Programming

count recent matches

helloSAS — Wed, 09 Nov 2016 05:00:09 GMT

I have provided DATA and RESULT tables below. I want to check for how many months an individual has been living in his current city. for example: John has been living in miami for last 4 months and Bob has been living in philadelphia for last 2 months.

How do I code to get the RESULT for the DATA I have?

DATA
Name	City	Month
john	philadelphia	201601
john	philadelphia	201602
john	philadelphia	201603
john	philadelphia	201604
john	philadelphia	201605
john	philadelphia	201606
john	miami	201607
john	miami	201608
john	miami	201609
john	miami	201610
bob	chicago	201601
bob	chicago	201602
bob	philadelphia	201603
bob	philadelphia	201604
bob	chicago	201605
bob	chicago	201606
bob	chicago	201607
bob	chicago	201608
bob	philadelphia	201609
bob	philadelphia	201610

RESULT
Name	City	Time_in_current_city
john	miami	4
bob	philadelphia	2

Re: count recent matches

Reeza — Wed, 09 Nov 2016 05:06:19 GMT

You edited your question so the answer met the question?

There's probably many ways, but using first/last is relatively straightforward.

data have;
informat name $12. city $20. period yymm6.;
input Name 	City  	Period ;
cards;
john philadelphia 201601
john philadelphia 201602
john philadelphia 201603
john philadelphia 201604
john philadelphia 201605
john philadelphia 201606
john miami   201607
john miami   201608
john miami   201609
john miami   201610
bob chicago 201601
bob chicago 201602
bob philadelphia 201603
bob philadelphia 201604
bob chicago 201605
bob chicago 201606
bob philadelphia 201607
bob philadelphia 201608
bob philadelphia 201609
bob philadelphia 201610
;
run;

proc sort data=have;
by name descending period;
run;

data want;
set have;
by name city notsorted;
retain flag num_months;
if first.name and first.city then flag=1;
if first.city and not first.name then do; flag=0;num_months=0; end;
num_months+flag;

if last.city and flag=1;

run;

Re: count recent matches

Ksharp — Wed, 09 Nov 2016 05:44:13 GMT


data have;
informat name $12. city $20. period yymmn6.;
input Name 	City  	Period ;
format period yymmn6.;
cards;
john philadelphia 201601
john philadelphia 201602
john philadelphia 201603
john philadelphia 201604
john philadelphia 201605
john philadelphia 201606
john miami   201607
john miami   201608
john miami   201609
john miami   201610
bob chicago 201601
bob chicago 201602
bob philadelphia 201603
bob philadelphia 201604
bob chicago 201605
bob chicago 201606
bob philadelphia 201609
bob philadelphia 201610
;
run;
data temp;
 do until(last.city);
  set have;
  by name city notsorted;
 end;
 flag=(intnx('month',today(),-1)=period);
 do until(last.city);
  set have;
  by name city notsorted;
  if flag then output;
 end;
 drop flag;
run;
proc freq data=temp;
 tables name*city/list out=want;
run;

Re: count recent matches

Ksharp — Wed, 09 Nov 2016 08:41:19 GMT


Maybe you want this :



data have;
informat name $12. city $20. period yymmn6.;
input Name 	City  	Period ;
format period yymmn6.;
cards;
john philadelphia 201601
john philadelphia 201602
john philadelphia 201603
john philadelphia 201604
john philadelphia 201605
john philadelphia 201606
john miami   201607
john miami   201608
john miami   201609
john miami   201610
bob chicago 201601
bob chicago 201602
bob philadelphia 201603
bob philadelphia 201604
bob chicago 201605
bob chicago 201606
bob philadelphia 201609
bob philadelphia 201610
;
run;
data temp;
 do until(last.city);
  set have;
  by name city notsorted;
 end;
 flag=last.name;
 do until(last.city);
  set have;
  by name city notsorted;
  if flag then output;
 end;
 drop flag;
run;
proc freq data=temp;
 tables name*city/list out=want;
run;