topic Re: SQL in SAS Programming

SQL: Find Consecutive Records

gaurav85 — Wed, 26 Jan 2022 07:12:09 GMT

There is a table with name and year, I want to fetch the name of the person who won consecutive award three years via SQL query.

Below is the result set.

Re: SQL

Reeza — Mon, 24 Jan 2022 16:57:52 GMT

Please post your data as text and include anything you've tried so far.

Happy to help but not really interested in typing out your data.

Ideally you would post it as a data step but for your first few even just text data is helpful.

@gaurav85 wrote:

There is a table with name and year, I want to fetch the name of the person who won consecutive award three years via SQL query.

Below is the result set.

Re: SQL

gaurav85 — Mon, 24 Jan 2022 17:07:49 GMT

Here is the data

Name Year
Raj 1999
Rahul 2000
Rahul 2001
Rahul 2002
Sachin 2003
Suraj 2004
Harish 2005
Nihal 2006
Jim 2007
Jack 2008
Jack 2009
Jack 2010
Madan 2011
Madan 2012
Madan 2013

output

Name
Jack
Madan
Rahul

Re: SQL

Reeza — Mon, 24 Jan 2022 17:10:59 GMT

And anything you've tried so far?

And why the SQL limitation? This is trivial in a data step whereas it's much more complicated in SQL.

Re: SQL

gaurav85 — Mon, 24 Jan 2022 17:13:03 GMT

Hi Reeza,

I am aware of the data step solution, In this particular case its a explicit ask to use SQL. I am not getting anything as of now in SQL

Re: SQL

LinusH — Tue, 25 Jan 2022 07:26:24 GMT

Forgive me, but this sounds kind of fabricated task, is this a home assignment?

Otherwise the SQL requirement doesn't make sense.

As @Reeza states, the data step is a far better solution for this task, not sure that you even can accomplish this with reasonable effort in SAS SQL. Without thinking it through I would start by grouping on name, compare min and max year with no records...not sure that would cover different scenarios though...?

Re: SQL: Find Consecutive Records

Kurt_Bremser — Tue, 25 Jan 2022 09:00:24 GMT

If you are working with SAS, the data step is always available, so the SQL requirement makes NO sense AT ALL.

If this needs to be done on a DBMS, that DBMS's SQL will most probably provide a tool to work with sequences.

In SAS SQL, you need to join the table with itself on name and (year - 1) and (year - 2) and then check for missing name in the joined tables:

proc sql;
create table want as
  select distinct t1.name
  from
    have t1
    left join have t2 on t1.name = t2.name and t2.year = t1.year - 1
    left join have t3 on t1.name = t3.name and t3.year = t1.year - 2
  where not missing(t2.name) and not missing(t3.name)
;
quit;

This will, of course, not perform as well as a data step because of the necessary internal sort.

I ran a test with some larger data (200k+ names), the SQL needed 6 seconds, a data step less than 1.

Re: SQL: Find Consqcutive Records

Ksharp — Tue, 25 Jan 2022 13:09:35 GMT

As Reeza said why SQL, pick up the right tool .

data have;
input Name $ Year;
cards;
Raj 1999
Rahul 2000
Rahul 2001
Rahul 2002
Sachin 2003
Suraj 2004
Harish 2005
Nihal 2006
Jim 2007
Jack 2008
Jack 2009
Jack 2010
Madan 2011
Madan 2012
Madan 2013
;

proc sql;
create table want as
select distinct name
 from have as a
  where (select count(distinct year) from have where name=a.name and year between a.year-2 and a.year)=3 ;
quit;