topic Re: derive last record in SAS Programming

derive last record

thanikondharish — Sun, 02 Sep 2018 12:37:40 GMT

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

Re: derive last record

PaigeMiller — Sun, 02 Sep 2018 12:42:11 GMT

Two step process

Delete records with missing values
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&docsetVersion=9.4&locale=en

Re: derive last record

thanikondharish — Sun, 02 Sep 2018 12:45:31 GMT

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

Re: derive last record

Kurt_Bremser — Sun, 02 Sep 2018 13:25:32 GMT

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

Re: derive last record

thanikondharish — Mon, 03 Sep 2018 04:49:31 GMT

without using where condition

Re: derive last record

error_prone — Mon, 03 Sep 2018 05:05:36 GMT

@thanikondharish wrote:
without using where condition

Why?

Re: derive last record

Kurt_Bremser — Mon, 03 Sep 2018 06:37:09 GMT

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.

Re: derive last record

Ksharp — Mon, 03 Sep 2018 13:59:11 GMT

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;