topic Re: PROC SQL in SAS Procedures

PROC SQL

pw7632 — Thu, 19 Mar 2020 14:27:40 GMT

SELECT DISTINCT

"player_id",

FIRST_VALUE("dob") OVER (PARTITION BY "player_id"

ORDER BY "player_id" ASC,

"completion_dte" DESC,

"plan" ASC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "dob",

I am trying to figure out this code but it was not done with PROC SQL. Does anyone know what this statement is doing/how would it translate to SAS. Thanks!

Re: PROC SQL

AndyBeans — Thu, 19 Mar 2020 14:46:13 GMT

If this is MS SQL Server that takes the first observation for each player_id, according to the sort order after the ORDER BY statement.

An equivalent in SAS would be a "first." operation on a sorted datastep, like:

PROC SORT DATA=DS1
BY player_id DESCENDING completion_dte plan;
RUN;
DATA DS2;
SET DS1;
BY player_id DESCENDING completion_dte plan;
if first.player_id then output;
RUN;

I might have the "DESCENDING" in the wrong place, I usually do.

Re: PROC SQL

bobpep212 — Thu, 19 Mar 2020 16:11:46 GMT

Pretty sure you got the descending in the right spot. I'm just trying to figure out how a player_id could have two different DOBs, unless they aren't talking about dates of birth.