Hi,   I have an extract like this with multiple bans and records. It is sorted by ban and ent_seq_no. What i want to do is only keep records for a ban after the DF step only (including DF). So in this case the first 3 records would be deleted...   I am thinking that deleting the records where ent_seq_no is less than the seq no for the record that has code DF is the way but i am not sure how to write it...   BAN ENT_SEQ_NO COL_ACTV_CODE COL_ACTV_DATE 10987 941020677 D2 04JUL2019:00:00:00 10987 941953583 D3 10JUL2019:00:00:00 10987 942384352 3S 12JUL2019:00:00:00 10987 942902222 DF 16JUL2019:00:00:00 10987 943965758 E 23JUL2019:00:00:00 10987 944352184 1S 25JUL2019:00:00:00 ... View more

Hi All,   I have 2 queries I need to execute one after another:   First: I need to pull data for the current month in a pass through... the date is in datetime format:   This is what I have tried so far without any results.... I know that I can specify the dates but I am trying to make it so that I don't need to so it can be automated   %Let today = %sysfunc(date(),date9.); %let sysmonth= %sysfunc(month("&sysdate"d)); %let sysyear= %sysfunc(year("&sysdate"d)); PROC SQL; connect to oracle (user=&name pass=&pass path=Exa); create table DF as select * from connection to Oracle (select * from tables where month(ACTV_DATE) = &sysmonth. ) ; disconnect from Oracle; quit;   second step.. all these records then have to pull data from a second table where the date ACTV_DATE is after the date in the second table. Again all dates are in datetime format     PROC SQL; connect to oracle (user=&name pass=&pass path=ExaODIN); create table DF1 as select * from connection to Oracle (select * from activity where ID in (select IDfrom df) and datepart(actv_date) >= datepart(secondtable_date) ; disconnect from Oracle; quit;   I usually use pass throughs to pull data... i haven't tried segmentation at this level. Really appreciate your help ... View more

There is data that I have sorted using the following code:   proc sort data=RESTORED ; by ID ent_seq_no descending col_actv_code ; quit;   I get the following. below is an example of a type of group in the data. What I want to do is delete these records( all of them) if the last actv_code = 'S'. I only want to delete the group of ID's if the last code is S. I cannot figure out how.   ID      ENT_SEQ_NO  ACTV_CODE 0935     913240851          S 0935     913863008            K 0935     915287558          S 0935     915733042          K 0935     919660717          S     Data r ; set restored; by ban ent_seq_no descending col_actv_code; if not last.ban then delete; run;   Any direction is appreciated. ... View more