BookmarkSubscribeRSS Feed
SrikanthY
Calcite | Level 5

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
;

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

SrikanthY
Calcite | Level 5
No its not working. getting zero observations

##- Please type your reply above this line. Simple formatting, no
attachments. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
SrikanthY
Calcite | Level 5
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. -##
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1381 views
  • 0 likes
  • 3 in conversation