topic Re: How to get nth value by group in SAS Programming

How to get nth value by group

SrikanthY — Wed, 09 Nov 2016 12:58:48 GMT

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
;

Re: How to get nth value by group

RW9 — Wed, 09 Nov 2016 13:08:24 GMT

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.

Re: How to get nth value by group

SrikanthY — Wed, 09 Nov 2016 13:12:50 GMT

No its not working. getting zero observations

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: How to get nth value by group

RW9 — Wed, 09 Nov 2016 13:20:50 GMT

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;

Re: How to get nth value by group

SrikanthY — Wed, 09 Nov 2016 13:24:50 GMT

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. -##

Re: How to get nth value by group

RW9 — Wed, 09 Nov 2016 13:29:39 GMT

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;

Re: How to get nth value by group

Ksharp — Thu, 10 Nov 2016 03:33:39 GMT


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;