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 |
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;
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.