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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.