[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    SAS to SQL (first.)
From:       Mahesh <peesari.mahesh () GMAIL ! COM>
Date:       2023-07-17 22:21:54
Message-ID: 1091980117437147.WA.peesari.maheshgmail.com () listserv ! uga ! edu
[Download RAW message or body]

Hi All,

Need some help in writing equivalent code to SAS in SQL. I need to drop the \
population if they had procedure within 30day limit. i have to take the latest svc_dt \
to check the 30day gap.

SAS Code:

data work.t1;
set work.have;
by num proc_cd svc_dt desc;
retain curr_dt;
if first.proc_cd then curr_dt = svc_dt;
if not first.proc_cd and curr_dt - svc_dt <=30 then delete;
else curr_dt = svc_dt;
run;

here is the data;
num.              proc_cd.     svc_dt 
==============================
123                     abc           4/5/2023
123                     abc           3/15/2023
123                     abc           3/1/2023
123                    abc           2/2/2023
123                    abc           1/10/2023
456                   pqr            3/18/2023

O/P Needed
=============================
123                     abc           4/5/2023
123                     abc           3/1/2023
123                    abc           1/10/2023
456                   pqr            3/18/2023

i tried below Teradata SQL code but it doesnt work perfectly,  not close:

select tbl3.* from ( select tbl2.* prev_svc_dt - svc_dt as dt_diff, case when \
prev_svc_dt _ svc_dt <=30 then   lag(svc-dt,2) over (partition by num ,proc_cd order \
by svc-dt desc) as new_dt,  new_dt - svc_dt as new_new_dt from ( select tbl1.*,
  row_number( over partition by num,proc_cd order by svc-dt desc) as rw_num,
  lag(svc_dt) over(partition by num,proc_cd order by svc_dt desc) as prev_svc_dt from \
t1 tbl1 ) tbl2) tbl3;

thanks


[prev in list] [next in list] [prev in thread] [next in thread]