BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
helloSAS
Obsidian | Level 7

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

3 REPLIES 3
Reeza
Super User

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

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;


Ksharp
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 952 views
  • 1 like
  • 3 in conversation