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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.