Mimicking SQL window functions with the DATA step?

tomcmacdonald — Wed, 01 Aug 2018 16:02:09 GMT

I need to test that a column is monotonic within a group. In other words that for a given group each subsequent row has an ascending number. So if id = 100 and there are three rows it will have 1, 2, 3. With PostgreSQL I would use a window function. Something like this:

CREATE TEMPORARY TABLE foo (
	id integer,
	val integer 
);

INSERT INTO foo VALUES (1, 1), (1, 2), (1, 3), (2, 1), (3, 1), (3, 2);

SELECT
	id,
	CASE
		WHEN LAG(val) OVER (PARTITION BY id) = val - 1 THEN TRUE
		WHEN LAG(val) OVER (PARTITION BY id) IS NULL THEN TRUE
		ELSE FALSE
	END AS passed
FROM foo;

Is there any equivalent function in SAS? The SAS lag function does not work with group partitions like in SQL.

Re: Mimicking SQL window functions with the DATA step?

Tom — Wed, 01 Aug 2018 16:16:14 GMT

It is much easier using a data step since SAS will process the records in order.

Do you want to consider the first value for each ID to be PASSED or not?

data want ;
  set have ;
  by id val ;
  passed = dif(val)=1 ;
  if first.id then passed=1 ;
run;

topic Re: Mimicking SQL window functions with the DATA step? in SAS Programming

Mimicking SQL window functions with the DATA step?

Re: Mimicking SQL window functions with the DATA step?