topic Re: First dot and last dot conversion into proc sql in SAS Programming

First dot and last dot conversion into proc sql

karrora1201 — Thu, 28 Jan 2021 13:34:58 GMT

Hi Everyone, Can anybody help me with the below issue?

-> wanted to convert the following datastep (containing first. last. functionality) into Proc sql

data readin;
input ID Name $ Score;
cards;
1     David   45
1     David   74
2     Sam     45
2     Ram     54
3     Bane    87
3     Mary    92
3     Bane    87
4     Dane    23
5     Jenny   87
5     Ken     87
6     Simran  63
8     Priya   72
;
run;

proc sort data=readin;
by ID Name;
run;

data b;
set readin;
by ID Name;
if last.ID or last.Name then output;
run;

Re: First dot and last dot conversion into proc sql

Ksharp — Thu, 28 Jan 2021 13:43:42 GMT

data readin;
input ID Name $ Score;
cards;
1     David   45
1     David   74
2     Sam     45
2     Ram     54
3     Bane    87
3     Mary    92
3     Bane    87
4     Dane    23
5     Jenny   87
5     Ken     87
6     Simran  63
8     Priya   72
;
run;

proc sql;
create table want as
select distinct *
 from (select monotonic() as _n_,* from readin)
  group by id,name
   having _n_=max(_n_);
quit;

Re: First dot and last dot conversion into proc sql

PaigeMiller — Thu, 28 Jan 2021 13:46:18 GMT

The only problem using the MONOTONIC() function in PROC SQL is that it is unsupported. It works today, it may not work in the next release (or some future release) of SAS. And so, if you are writing code with an unsupported feature, this can lead to problems and the original problem is probably best left as a DATA step.

Re: First dot and last dot conversion into proc sql

Astounding — Thu, 28 Jan 2021 13:46:25 GMT

There is no such thing. SQL does not guarantee the order in which it selects observation, so the "last" observation is unreliable. It could change from run to run.

There is an unsupported, unguaranteed "monotonic" feature of SQL if you want to go that route.

Re: First dot and last dot conversion into proc sql

Kurt_Bremser — Thu, 28 Jan 2021 13:56:24 GMT

Maxim 14: Use the Right Tool, which is the data step for such tasks. SQL code would be much more complicated, harder to maintain, and have worse performance. SQL does not have the concept of sequence that the data step has, and so that must be forced, usually with a sub-select which has very bad performance, or an intermediate step that creates a sequence number through use of the undocumented (and therefore not advisable) MONOTONIC() function.

BTW last.ID implies last.Name, so it is sufficient to check for last.Name in your example.

A possible SQL code might be

proc sql;
create table want as
  select
    id,
    name,
    score
  from (
    select *, monotonic() as seq
    from readin
  )
  group by id, name
  having seq = max(seq)
;
quit;

but that is NOT guaranteed to create the same result as the SORT/DATA steps.

Re: First dot and last dot conversion into proc sql

novinosrin — Thu, 28 Jan 2021 14:05:54 GMT

I agree to both @Astounding and @PaigeMiller for their professional neat advice. Genius @Ksharp's solution is something that came to my mind too. However, this certainly is a not a question that's production code related and a fun question perhaps?

The subject matter though that's worth learning is whether or not there is a clean way of reading a dataset in a sequential order. Once we have this, a possible conditional construct utility may surface. I'm afraid Proc SQL doesn't quite have the row order functionality and in SQL of any variant requires explicit determination of a row order unlike the datastep.

The author of the best-selling SQL book of all time Ben Forta writes, "the retrieved data from underlying tables is not displayed in a mere random order. If unsorted, data will typically be displayed in the order in which it appears in the underlying tables. This could be the order in which the data was added to the tables initially. However, if data was subsequently updated or deleted, the order will be affected by how the DBMS reuses reclaimed storage space. The end result is that you cannot (and should not) rely on the sort order if you do not explicitly control it. Relational database design theory states that the sequence of retrieved data cannot be assumed to have significance if ordering was not explicitly specified"

Note: Some even go as far to believe almighty perhaps created EF Codd to relate and normalise tables and then Ben forta to play with it. If Ben wrote it, I believe him.