topic First and Last in proc sql in SAS Programming

First and Last in proc sql

raja777pharma — Mon, 05 Apr 2021 05:59:32 GMT

Hello ,

I am try to write code in Proc sql for below data step , but i am not getting as results in data step vs proc sql.

My data step:

data last_ass_dt;
  set all_results;
  by usubjid rsdt;
  if first.usubjid;
keep usubjid rsdt;
run;

My testing proc sql code:

proc sql;
   create table last_ass_dt as
   select usubjid,rsdt,'last Assesment date' as ByGroup
   from all_results a1
   where rsdt = (select min(rsdt)
                     from all_results a2
                     where a1.rsdt = a2.rsdtc)
  order by usubjid,rsdt;
quit;

please let me know what i am missing in PROC SQL code.

Thank you,

Raja.

Re: First and Last in proc sql

PeterClemmensen — Mon, 05 Apr 2021 06:23:03 GMT

Why not just do this in the Data Step? Seems much easier.

However, try this (untested)

proc sql;
   create table last_ass_dt as
   select usubjid, rsdt
   from all_results
   group by usubjid
   having min(rsdt) = rsdt;
quit;

Re: First and Last in proc sql

PaigeMiller — Mon, 05 Apr 2021 12:03:07 GMT

@PeterClemmensen wrote:

Why not just do this in the Data Step? Seems much easier.

However, try this (untested)
proc sql;
   create table last_ass_dt as
   select usubjid, rsdt
   from all_results
   group by usubjid
   having min(rsdt) = rsdt;
quit;

MIN is not the same as first.variable (and I don't know if it works for character variables)

To @raja777pharma , this is a feature that exists only in a DATA step, so you really need to use a DATA step. There is really no equivalent feature in SQL.

Re: First and Last in proc sql

ballardw — Mon, 05 Apr 2021 15:18:26 GMT

SQL is based on SET operators and as such really doesn't have a native concept of "first" or "last" for values in a subset.

If you did around the internet you will find that the order of records in a query can be seriously changed by code optimizers working in the background to make a query execute more efficiently.

So if you get something that works one time for SQL it may not the next time with different data.