Code for selecting specific observation for each individual

Reply
Occasional Contributor
Posts: 16

Code for selecting specific observation for each individual

Hi Team,

I am learning SAS and working on some task.Below is the dataset that I have.


idtimenameincomegender
110:00 AMSandeep420001
111:00 AMSandeep320001
111:23 AMSandeep120001
111:39 AMSandeep220001
111:55:00 AMSandeep230001
27:00 AMnaresh420002
28:00 AMnaresh320002
29:23 AMnaresh120002
210:39 AMnaresh220002
210::50 AMnaresh230002
35:00 PMSuresh1420002
36:00 AMSuresh1320002
37:23 PMSuresh1120002
38:39 PMSuresh1220002
311::00 PMSuresh2300002

Now, I want to report the 3rd observation from each individual.I have sorted the dataset on name and time.Then I am unable to get it.

Can someone give some hints to solve it.

proc sort data=one out=new;

by name time  ;run;

Thank you,

Sandeep

Contributor
Posts: 37

Re: Code for selecting specific observation for each individual

Posted in reply to Sandeepkumar

data have

;

input id time time10. name $ income gender $

;

format  time time.;

cards;

1 10:00:00 Sandeep 42000 1

1 11:00:00 Sandeep 32000 1

1 11:23:00 Sandeep 12000 1

1 11:39:00 Sandeep 22000 1

1 11:55:00 Sandeep 23000 1

2 07:00:00 naresh 42000 2

2 08:00:00 naresh 32000 2

2 09:23:00 naresh 12000 2

2 10:39:00 naresh 22000 2

2 10:50:00 naresh 23000 2

3 17:00:00 Suresh 142000 2

3 18:00:00 Suresh 132000 2

3 19:23:00 Suresh 112000 2

3 20:39:00 Suresh 122000 2

3 23:00:00 Suresh 230000 2

;

run;

proc sort data = have ; by name time ;run;

proc print data = have ;run;

data want(drop = flg)

;

set have

;

by name time ;

retain flg ;

if first.name then flg = 1 ;

else flg + 1;

if flg = 3 then output ;

run;

proc print data = want ; run;

Converted the time variable to be in a standard format in order to be sorted by time.

Thanks

Occasional Contributor
Posts: 16

Re: Code for selecting specific observation for each individual

Posted in reply to Karthikeyan

Thank you so much Karthikeyan.

Occasional Contributor
Posts: 16

Re: Code for selecting specific observation for each individual

Posted in reply to Karthikeyan

Hi Karthikeyan,

I have one more query.Suppose in the dataset the individuals may not be same.Like 5 iterations for one person,4 iterations for other person and so on.In that case,if I want to capture the nth observation.Will the code still remain same.

Contributor
Posts: 37

Re: Code for selecting specific observation for each individual

Posted in reply to Sandeepkumar

Irrespective of how many records you see for a person , the code will always pick the nth record. The only case where you need to worry about is , what if they don't have a nth record or more.

like you want 3rd record for each person , but a particular person  appears only twice. In such case he won't get picked

Occasional Contributor
Posts: 16

Re: Code for selecting specific observation for each individual

Posted in reply to Karthikeyan

Great friend.Got it.

Thank you very much for your time.

Super User
Super User
Posts: 7,955

Re: Code for selecting specific observation for each individual

Posted in reply to Sandeepkumar

Personally I would recommend assigning a sequence number per group:

data want;

     set have;

     by id;

     retain seq;

     if first.id then seq=1;

     else seq=seq+1;

run;

Based of that sequence number you can then select any observation within the group you need.  The second reason why you would want to assign a sequence number is repeatability.  So say you have 3 records for id 1 on run one, if you have 6 on run two, the third observation may not remain in the 3rd row, hence your not able to repeat the original code.  Not sure what industry your in but all CDISC standards and OC setups and such like generally always have a sequence associated with a record so you can always (irrespective of changes) repeat code.

Occasional Contributor
Posts: 16

Re: Code for selecting specific observation for each individual

Sure.I will try this code as well.Thank you.

Super User
Posts: 11,343

Re: Code for selecting specific observation for each individual

Posted in reply to Sandeepkumar

If the desire is to get the last record of each group in the last scenario:

data want;

     set have;

     by id;

     retain seq;

     if last.id;

run;

Ask a Question
Discussion stats
  • 8 replies
  • 342 views
  • 0 likes
  • 4 in conversation