You can submit a pass-through query that does everything you want. Should be quite fast if SQL Server table is enormous because no detail records are passed into SAS for processing, only the final result set.
Example:
A deep nested query joins of rows of zero amounts to rows of nonzero amount. That join result is measured groupwise with row_number () over each id, and that finally selected for the first row of the group which is the row of non-zero amount first transitioning from a row of a zero amount.
SQL Passthrough
CREATE TABLE have
(id integer, id2 integer, amount real, [date] date)
;
INSERT INTO have
VALUES
(1, 10, 0, '2020-08-20')
, (2, 10, 0, '2020-08-20')
, (3, 10, 0, '2020-08-20')
, (4, 10, 0, '2020-08-20')
, (5, 10, 50, '2020-08-20')
, (1, 10, 50, '2020-08-21')
, (2, 10, 50, '2020-08-21')
, (3, 10, 50, '2020-08-21')
, (4, 10, 0, '2020-08-21')
, (1, 10, 50, '2020-08-22')
, (2, 10, 50, '2020-08-22')
, (3, 10, 50, '2020-08-22')
, (4, 10, 50, '2020-08-22')
;
Query
select * from
( select
seek.* ,
row_number() over (
partition by id
order by [date]
) [seqnum]
from
(
select
[nzeros].*
from
( select id, [date] from have where amount = 0 ) [zeros]
right join
( select id, [date] from have where amount > 0 ) [nzeros]
on
[zeros].id = [nzeros].id
and [zeros].date < [nzeros].date
) [seek]
) [groups]
where
[groups].seqnum = 1
;
... View more