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 |
Two step process
In the set statement, use a where= dataset option to get rid of the missing values.
@thanikondharish wrote:
without using where condition
Why?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.