Finding the location (home/work) with highest number of visits.

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Finding the location (home/work) with highest number of visits.

Hi guys,

I have a transportation data where i am trying to get location of home (first highest visits) and work (second highest visits) . Below is the description of the typical dataset. First column is the person ID for each individual. The second variable is a locational identifier (a specific location ID) which could be work/play/leisure etc (i dont know that yet). The third variables is the number of times the person (given by personID) has visited that particular location ID. I want to assume that the first highest visit is their home and second is their work place.

person_IDlocation_IDvisits
11209222
12323454
136786540
137997832
15646651
11123445
22565432
21123447
21209227
223234554
236786529
237997815
25646655
24333323

I want to write a code where which gives me an output like this , where i want the person ID, his home location which is the place where he visited the most ,work location, second highest vist location ID and misc which is third highest!

person_IDhomeID(firstH)visits_hworkID(secondH)visits_wmiscID(ThirdH)visit_m
136786540379978321123445
2232345543678652937997815

Can someone help me write a simple code for this? I am new to sas, i want to learn how to code for a problem like this. thanks guys Smiley Happy

-jessica


Accepted Solutions
Solution
‎02-05-2015 12:32 AM
Grand Advisor
Posts: 17,438

Re: Finding the location (home/work) with highest number of visits.

1. First sort your data by Person_ID and Visits (PROC SORT)

2. Transpose your data using a single data step or two proc transposes.

Here's a basic example - this is a frequently asked question.

http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

Your data is slightly different so here's a sketch of the code (untested):

data want;

set have;

by person_ID visits;

Array IDs(3) ID_home ID_work ID_misc;

Array visits(3) visits_h visits_w visits_m;

retain ID_home ID_work ID_misc visits_h visits_w visits_m;

*Set count to 1 and array variables to missing at each new ID;

if first.person_ID then do;

     count=1;

    call missing (ID_home, ID_work, ID_misc, visits_h, visits_w, visits_m);

end;

else count+1;

*If one of the first three records then output;

if count<=3 then do;

IDs(count)=location_id;

visits(count)=visits;

end;

if last.person_ID then output;

keep person_ID ID_: visits_:;

run;

View solution in original post


All Replies
Solution
‎02-05-2015 12:32 AM
Grand Advisor
Posts: 17,438

Re: Finding the location (home/work) with highest number of visits.

1. First sort your data by Person_ID and Visits (PROC SORT)

2. Transpose your data using a single data step or two proc transposes.

Here's a basic example - this is a frequently asked question.

http://www.ats.ucla.edu/stat/sas/modules/longtowide_data.htm

Your data is slightly different so here's a sketch of the code (untested):

data want;

set have;

by person_ID visits;

Array IDs(3) ID_home ID_work ID_misc;

Array visits(3) visits_h visits_w visits_m;

retain ID_home ID_work ID_misc visits_h visits_w visits_m;

*Set count to 1 and array variables to missing at each new ID;

if first.person_ID then do;

     count=1;

    call missing (ID_home, ID_work, ID_misc, visits_h, visits_w, visits_m);

end;

else count+1;

*If one of the first three records then output;

if count<=3 then do;

IDs(count)=location_id;

visits(count)=visits;

end;

if last.person_ID then output;

keep person_ID ID_: visits_:;

run;

Grand Advisor
Posts: 9,596

Re: Finding the location (home/work) with highest number of visits.

If I understood what you mean.

data have;
input person_ID     location_ID     visits ;
cards;
1     120922     2
1     232345     4
1     367865     40
1     379978     32
1     564665     1
1     112344     5
2     256543     2
2     112344     7
2     120922     7
2     232345     54
2     367865     29
2     379978     15
2     564665     5
2     433332     3
;
run;
proc sort data=have ;by  person_ID descending visits;run;
data temp;
 set have;
 by  person_ID;
 length vname_id vname_visit $ 20;
 if first.person_ID then n=0;
 n+1;
 select(n);
  when(1) do;vname_id='HomeID'; vname_visit='visits_h';end;
  when(2) do;vname_id='WorkID'; vname_visit='visits_w';end;
  when(3) do;vname_id='MiscID'; vname_visit='visits_m';end;
  otherwise;
 end;
 if n lt 4;
run;
proc sql;
 select distinct catt('temp(where=(n=',n,') rename=(location_ID=',vname_id,' visits=',vname_visit,'))')  into : list separated by ' '
  from temp;
quit;
data want;
 merge &list ;
 by person_ID;
 drop vname_: n;
run;

Xia Keshan

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 216 views
  • 4 likes
  • 3 in conversation