derive last record

[thanikondharish]

subid	Date	Dose
P101	13JAN2005	Asp-05mg
P101	19JAN2005	Asp-10mg
P101	25JAN2005	Asp-15mg
P101	31JAN2005
P102	14JAN2005	Asp-10mg
P102	20JAN2005	Asp-05mg
P102	26JAN2005	Asp-15mg
P102	01FEB2005	Asp-15mg
P103	19JAN2005	Asp-15mg
P103	25JAN2005	Asp-10mg
P103	30JAN2005	Asp-10mg
P104	26JAN2005	Asp-10mg
P104	02FEB2005	Asp-10mg
P105	02FEB2005	Asp-20mg

derive every group last record without missing like see below dataset

subid	Date	Dose
P101	25JAN2005	Asp-15mg
P102	01FEB2005	Asp-15mg
P103	30JAN2005	Asp-10mg
P104	02FEB2005	Asp-10mg
P105	02FEB2005	Asp-20mg

[PaigeMiller]

Two step process

1. Delete records with missing values
2. Use the LAST. structure in a SAS data step to find the last record in each group https://documentation.sas.com/?docsetId=lrcon&docsetTarget=n01a08zkzy5igbn173zjz82zsi1s.htm&docsetVe...

[thanikondharish]

yes i did what you mentioned like two step process
is there any method in single step

[Kurt_Bremser]

In the set statement, use a where= dataset option to get rid of the missing values.

[thanikondharish]

without using where condition

[error_prone]

---

@thanikondharish wrote:
without using where condition

---

Why?

[Kurt_Bremser]

If you want to act on a condition, you need to program it. Don't be an idiot here.

data have;
infile cards truncover;
input subid :$4. date: date9. dose :$8.;
format date date9.;
cards;
P101 13JAN2005 Asp-05mg
P101 19JAN2005 Asp-10mg
P101 25JAN2005 Asp-15mg
P101 31JAN2005 
P102 14JAN2005 Asp-10mg
P102 20JAN2005 Asp-05mg
P102 26JAN2005 Asp-15mg
P102 01FEB2005 Asp-15mg
P103 19JAN2005 Asp-15mg
P103 25JAN2005 Asp-10mg
P103 30JAN2005 Asp-10mg
P104 26JAN2005 Asp-10mg
P104 02FEB2005 Asp-10mg
P105 02FEB2005 Asp-20mg
;
run;

data want;
set have (where=(dose ne ''));
by subid;
if last.subid;
run;

proc print data=want noobs;
run;

Result:

subid         date      dose

P101     25JAN2005    Asp-15mg
P102     01FEB2005    Asp-15mg
P103     30JAN2005    Asp-10mg
P104     02FEB2005    Asp-10mg
P105     02FEB2005    Asp-20mg

Problem solved, case closed.

[Ksharp]

Is there any reason you are unable to use WHERE ? or just want check our sas skill ?

data have;
infile cards truncover;
input subid :$4. date: date9. dose :$8.;
format date date9.;
cards;
P101 13JAN2005 Asp-05mg
P101 19JAN2005 Asp-10mg
P101 25JAN2005 Asp-15mg
P101 31JAN2005 
P102 14JAN2005 Asp-10mg
P102 20JAN2005 Asp-05mg
P102 26JAN2005 Asp-15mg
P102 01FEB2005 Asp-15mg
P103 19JAN2005 Asp-15mg
P103 25JAN2005 Asp-10mg
P103 30JAN2005 Asp-10mg
P104 26JAN2005 Asp-10mg
P104 02FEB2005 Asp-10mg
P105 02FEB2005 Asp-20mg
;
run;
data want;
 do i=1 by 1 until(last.subid);
   set have;
   by subid;
   if not missing(dose) then _i=i;
 end;
 do i=1 by 1 until(last.subid);
   set have;
   by subid;
   if _i=i then output;
 end;
 drop i _i;
 run;