DATA Step, Macro, Functions and more

count recent matches

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

count recent matches

[ Edited ]

 

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

Accepted Solutions
Solution
‎11-09-2016 10:58 AM
Super User
Posts: 9,691

Re: count recent matches


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;

View solution in original post


All Replies
Super User
Posts: 17,942

Re: count recent matches

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;
Super User
Posts: 9,691

Re: count recent matches


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;


Solution
‎11-09-2016 10:58 AM
Super User
Posts: 9,691

Re: count recent matches


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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 169 views
  • 1 like
  • 3 in conversation