Hi @Florent  The discussion forum SAS Macro Facility, Data Step and SAS Language Elements sounds interesting, but I haven't been able to find it anywhere in communities.sas.com. May I ask for directions to get there?   thanks ... View more

Hi @David_Billa    The syntax is (where=( expression ));   So make sure you have balanced parantheses in the expresson, so the number of right parantheses is equal to the number of right parantheses, so you don't by accident have an extra paranthesis that ends the where=( . ... View more

Hi @mws1985    The problem is that a DI Studio Job job in another DI Studio Job is not treated as an individual job. The contained transformations are included as if they were written in the master job, and the DI Studio "job envelope" where the jobrc macro is defined, and the variable job_rc is initiated to zero, appears only once.   So I think that you will get the desired result if you include a precode in each job where the job_rc macro variable is initiated to zero by calling %rcSet(0).    The downside is that the master job only fails if the last sub-job fails. If you want the master job to fail in case of any error, it becomes a little more complicated. Besides resetting the job_rc macro variable as mentioned, add a new master_rc macro variable:   Include master job precode containing a %rcMasterSet macro definition similar to the %rcset macro definition. Include a call to update master_rc from job_rc in each job's postcodein addition to your existing code. Include master job postcode to update the macro variable syscc from master_rc to get the job to fail if any subjobs are failed.   You cannot do the opposite: leave the job_rc "as is" and just create your own sub_rc to keep track of each sub-job, because job_rc is updated for every individual transformation, and I see no way to create a similar functionality. I am sorry to say that this is not tested, but I think it would work. Pl3ease write if you have any questions or it doesn't work as intended.          ... View more

Hi @AmitParmar  I have tried to make a different code, you could use for experiments. It is split up in two parts: The first data step performs a full analysis, and the next extracts relevant records based on the analysis.    data have; informat Visit_Date anydtdte.; format Visit_Date date.; input ID$ Visit_Date; cards; A 24-Jul-15 A 13-Aug-15 A 12-Sep-15 A 16-Dec-15 A 15-Mar-16 B 24-Jul-15 B 22-Oct-15 B 25-Jan-16 B 04-Feb-16 B 15-Mar-16 ; run; * Sort data to make sure input is ordered for further processing; proc sort data=have; by ID Visit_Date; run; * Perform af full analysis; * Using LAG function to get previous date and Look-ahead SET statement to get nex date; data temp (drop=Next_ID); set have end=eof; by ID; format Prev_Visit_Date date.; * Initiate break point variables; Retain Break_Before Break_After; if first.ID then call missing(Break_Before, Break_After); * Calculate difference to previous record within same ID; Prev_Visit_Date = lag(Visit_Date); if not first.ID then Diff_To_Prev = (Visit_Date - Prev_Visit_Date); else Diff_To_Prev = .; * Get values from next record; if not eof then set have ( firstobs=2 keep=Visit_Date ID rename=(ID = Next_ID Visit_Date=Next_Visit_Date)); * Calculate difference to next record within same ID; if not last.ID then Diff_To_Next = (Next_Visit_Date - Visit_Date); * Set Break Points; if Diff_To_Prev > 50 then Break_Before + 1; else Break_Before = .; if Diff_To_Next > 50 then Break_After + 1; else Break_After = .; run; * Get wanted data; * This gives the first pair; data want; set temp; keep ID Visit_Date; if Break_Before = 1 or Break_After = 1; run; * Get wanted data; * This gives all pairs, doubling records being in two pairs; data want2; set temp; keep ID Visit_Date; if Break_Before ne . then output; if Break_After ne . then output; run; ... View more

Hi @AmitParmar    Please check data a; length sdate edate$15.; input DRUG$ SDATE $ EDATE $; cards; A 24-Sep-19 03-Dec-19 A 17-Jan-20 21-Feb-20 A 21-MAY-20 20-Jun-20 B 30-Jun-20 29-Aug-20 B 13-OCT-20 12-NOV-20 ; run; * Convert the dates to SAS dates; * It must be "real" SAS dates to perform date calculations; * Internal SAS date value is the number of days since 01jan1960; data have (drop = sdate edate); set a; format Date_Start Date_End date.; Date_Start = input(sdate,anydtdte.); Date_End = input(edate,anydtdte.); run; * Sort data; * Date_End included in sort to get highest Date_End in case of more than one record with same drug/Date_Start; proc sort data=have; by DRUG Date_Start Date_End; run; data want (drop=Date_Start_current Date_Start_Next Drug_Next); set have (rename=(Date_Start=Date_Start_current)) end=eof; by DRUG; retain Date_Start; format Date_Start date.; if not eof then do; * Get values from next record; set have (firstobs=2 keep=Date_Start DRUG rename=(Date_Start=Date_Start_Next DRUG=Drug_Next)); end; if first.DRUG then Date_Start = Date_Start_Current; if Drug_Next ne DRUG or eof or (Date_Start_Next - Date_End) > 50 then do; output; Date_Start = Date_Start_Current; Date_Start_Next = .; end; else do; if Date_Start_Next = . then Date_Start_Next = Date_Start_Current; end; run; if this helps:   ... View more

Hi @AmitParmar    The general idea would be to look ahead using a second set statement with firstobs=2.  Save the start date and compare the end date to the start date in next record. If the difference > 60 or next record is a new drug then set start = saved start date and output. Otherwise continue.   If you provide working code to create the input data set I will make a working example for you. Your real data must contain other variables beside the drug and dates, and these will be taken from the last record unless something is done to save these too.    ... View more

Hi @gyambqt    You don't supply information about your setup, but if the flow is scheduled to run in LSF it can be triggered from the command line (or a SAS X command) using the command:                  jtrigger flow_name   I don't know any way to trigger the flow if the flow is defined in MC and nothing more.  ... View more

Hi @BrahmanandaRao  If you want a list of all variables that exist in ALL the selected tables, use this select: proc sql; select distinct name, count(*) as antal from dictionary.columns where libname='WORK' and memname EQT 'DS' group by name having antal = ( select count(distinct memname) from dictionary.columns where libname='WORK' and memname EQT 'DS' ); quit;     ... View more

Hi @BrahmanandaRao    You ask for a memname called DATA. To get your created data sets, try changing the select statement to: select * from dictionary.columns where libname='WORK' and memname EQT 'DS' This returns DS1 and DS3. You don't get a DS2, because you create DS1 twice. ... View more