DATA Step, Macro, Functions and more

How to get nth value by group

Reply
Occasional Contributor
Posts: 17

How to get nth value by group

I want immediate non missing observation value after last flag is Y. that is: visit 3rd of id A1001 and visit 4th of A1002


data x;
infile cards missover;
input id $ visit val flag $;
cards;
A1001 1 20 Y
A1001 2 20 Y
A1001 3 20
A1001 4 .
A1001 5 20
A1002 1 20
A1002 2 20 Y
A1002 3 .
A1002 4 20
;

Super User
Super User
Posts: 7,401

Re: How to get nth value by group

Not sure what the output should look like but something like:

data want;
  set x;
  by id;
  retain tmp_flg;
  if first.id then tmp_flg=0;
  if flag="" and tmp_flag then tmp_flag=1;
  else if tmp_flg=1 and val ne . then output;
run;

Not tested, but that should work.

Occasional Contributor
Posts: 17

Re: How to get nth value by group

No its not working. getting zero observations

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Super User
Posts: 7,401

Re: How to get nth value by group

There was a typo:

data x;
infile cards missover;
input id $ visit val flag $;
cards;
A1001 1 20 Y
A1001 2 20 Y
A1001 3 20
A1001 4 .
A1001 5 20
A1002 1 20
A1002 2 20 Y
A1002 3 .
A1002 4 20
;
run;
data want;
  set x;
  by id;
  retain tmp_flg;
  if first.id then tmp_flg=0;
  if flag="Y" then tmp_flg=1;
  if flag="" and tmp_flg=1 and val ne . then do;
    tmp_flg=2;
    final="Y";
  end;
run;
Occasional Contributor
Posts: 17

Re: How to get nth value by group

fine thank you. But I want to keep only those records in final dataset.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Super User
Posts: 7,401

Re: How to get nth value by group

Yes, but you didn't specify what the output should look like in your post, hence I was guessing.  Replace this line:

    final="Y";

 

With:

    output;
Super User
Posts: 9,681

Re: How to get nth value by group


data x;
infile cards missover;
input id $ visit val flag $;
cards;
A1001 1 20 Y
A1001 2 20 Y
A1001 3 20
A1001 4 .
A1001 5 20
A1002 1 20
A1002 2 20 Y
A1002 3 .
A1002 4 20
;
run;
data want;
 do i=1 by 1 until(last.id);
  set x;
  by id;
  if flag='Y' then _i=i;
 end;
 n=0;
 do j=1 by 1 until(last.id);
  set x;
  by id;
  if j gt _i and not missing(val) then n+1;
  if n=1 and not missing(val) then output;
 end;
drop i j n;
run;
Ask a Question
Discussion stats
  • 6 replies
  • 191 views
  • 0 likes
  • 3 in conversation