topic how can i convert this to SAS SQL in New SAS User

how can i convert this to SAS SQL

xander — Wed, 27 Dec 2023 10:37:32 GMT

i'm trying to conver my SQL to SAS but it gives me error in OUTER APPLY AND SELECT TOP 1

PROC SQL;
   SELECT a.'PlayerID'n, c.'GamingDate'n
      FROM (SELECT t1.'PlayerPatron ID'n FROM XXX.Player t1 GROUP BY t1.'PlayerID'n) a
   OUTER APPLY
      (SELECT TOP 1 * FROM 
   	     (SELECT t2.'PlayerID'n, t2.'GamingDate'n FROM XXX.FirsRating t2
		  UNION 
          SELECT t3.'PlayerID'n, t3.'Gamingdate'n FROM XXX.SecondRating t3) b
   WHERE a.'PlayerID'n = b.'PlayerID'n ORDER BY b.'PlayerID'n DESC, b.'GamingDate'n DESC) c
   WHERE GamingDate IS NOT NULL;
QUIT;

Re: how can i convert this to SAS SQL

Patrick — Wed, 27 Dec 2023 11:19:12 GMT

SAS SQL doesn't support this functionality and it would be rather hard and inefficient to express in SAS SQL only. It's certainly possible to write SAS code to achieve the same result.

Why do you need to convert to SAS SQL? If your source table is still in the database and you can access this data from SAS then you can also run the DB native SQL and just move the result set to SAS for further processing.

Re: how can i convert this to SAS SQL

SASKiwi — Thu, 28 Dec 2023 22:04:21 GMT

You don't need to convert it. Just run it as is in SQL Passthru in your original SQL database

libname db odbc datasrc=datasource schema = schema user=user password = password;

proc sql;
 connect using db;
  create table Want as 
  select * from connection to db
  ( SELECT a.'PlayerID'n, c.'GamingDate'n
      FROM (SELECT t1.'PlayerPatron ID'n FROM XXX.Player t1 GROUP BY t1.'PlayerID'n) a
   OUTER APPLY
      (SELECT TOP 1 * FROM 
   	     (SELECT t2.'PlayerID'n, t2.'GamingDate'n FROM XXX.FirsRating t2
		  UNION 
          SELECT t3.'PlayerID'n, t3.'Gamingdate'n FROM XXX.SecondRating t3) b
   WHERE a.'PlayerID'n = b.'PlayerID'n ORDER BY b.'PlayerID'n DESC, b.'GamingDate'n DESC) c
   WHERE GamingDate IS NOT NULL)
  ;
quit;

Re: how can i convert this to SAS SQL

Tom — Thu, 28 Dec 2023 22:23:55 GMT

What SQL dialect is it written for?

What the heck is an OUTER APPLY? That does not look like any flavor of SQL I have ever seen. I have never seen APPLY used in SQL code before.

The other I can at least make a guess as to the meaning as it looks similar to implementations of WINDOW operations (introduced into standard SQL much later than the version of SQL that SAS implements). But how can it work without an ORDER BY clause? How could it figure out which one is the "top" if they aren't ordered by something?

Also the first subquery makes no sense.

(SELECT t1.'PlayerPatron ID'n FROM XXX.Player t1 GROUP BY t1.'PlayerID'n)

You have a GROUP BY but you are not calling any aggregate functions that would use the grouping. And you aren't even selecting the variable that is used to form the grouping. What is that subquery supposed to return?

Re: how can i convert this to SAS SQL

Patrick — Thu, 28 Dec 2023 23:12:38 GMT

@Tom It's MS SQL

https://learn.microsoft.com/en-us/u-sql/statements-and-expressions/select/from/select-selecting-from-cross-apply-and-outer-apply

Summary

Often when processing some more complex value in a column, such as a byte array, a string, a MAP, ARRAY, JSON or XML document, one would like to extract more than just one value, such as a whole rowset of information per column value.

U-SQL provides the CROSS APPLY and OUTER APPLY operator which evaluates the rowset generating expressions on the right side against each row and its individual column cells of the rowset on the left. The result is the combination of the columns of both rowsets where the values of the left rowset get repeated for each result of the right rowset expression.

Re: how can i convert this to SAS SQL

Tom — Fri, 29 Dec 2023 00:53:25 GMT

Well that is as clear as mud. But it seems like it is just a LEFT JOIN.

Although the last WHERE seems to turn it into an INNER JOIN.

The TOP 1 makes it look like you only one one observation per BY group.

data want;
  merge left (in=in1) right1(in=in2) right2(in=in2);
  by ID ;
  if in1 ;
  if first.ID ;
run;