I am sorry for not much infromation at the begining. But i am still looking for solution. So... What i have : %macro report (bla,bla,bla); /*....part where i prepare FINAL dataset for report ...*/ /*...in .csv file which in attachment(draft example) presented terms of special interest which should be flaged in FINAL dataset...*/   %* Prepare Special list of AEs from ae_special.csv file; proc import datafile='/sas/common/dev/templates/progs/drt/medmon/ae_special1.csv' dbms=dlm out=special replace; delimiter="~"; getnames=yes; guessingrows=32767;/*maximum number of records*/ run;   data aesi_special; length term_pt $120; set special; if term ne '' then aesi='y'; term_pt=strip(lowcase(term)); term_1=cats('"',strip(term_pt),'"');   %* excluding duplicates in .csv file; proc sort nodupkey; by term; run;   data aesi_special1(keep=start label fmtname); set aesi_special (rename=(term_pt=start)); retain fmtname 'aesi' type 'I'; label=1; run; proc format cntlin = aesi_special1; run; MPRINT(MM_TEMPL_AE_FULL): proc format cntlin = aesi_special1; ERROR: This range is repeated, or values overlap: .-..    %*trying to use created format ...but it is n0t working (((; data FINAL; set ae1(where=(aeterm ^= '')); length  term_pt $120 aesi 8; aesi=input(strip(lowcase(aeterm_pt)),aesi.); proc sort; by aeterm_pt; run;   %mend report;   i tried to use next part of code but it is also not working ((( and left join/merge by term also does not give what i want((   proc sql noprint; select distinct term_1 into: _aesi separated by ' '  /* term_1 have values : "pulmonary oedema" "pain" ... etc. */ from aesi_special; quit;   data FINAL; set ae1(where=(aeterm ^= '')); length  term_pt $120 aesi 8; if lowcase(strip(aeterm_pt)) in (&_aesi) then aesi=1; proc sort; by aeterm_pt; run;   In attachment also screenshot of aeterm_pt which presented and some of them should be flagged in FINAL dataset ... View more

Then i want to use generated format in next step:   %* Final data step for ae report all aes to first sheet; data ae_rep; set ae1(where=(aeterm ^= '')); length firstline $1024 term_pt $120 aesi 8; aesi=input(strip(lowcase(aeterm_pt)),aesi.); proc sort; by term_pt; run;   In attachment list of terms and some of them(which are in .csv file) should be flagged. If there any other idea how to make if feasible inside the macro please let me know.Thank you all for help! ... View more

My goal is to create macro which will be generate standart report. So all actions should be available inside the macro. It works correctly but project specific thing is to find 'special terms' So thats why i asked for your help as i am stuck on this.   %* Prepare Special list of AEs from ae_special.csv file; proc import datafile='/sas/bla/bla/ae_special_1.csv' dbms=dlm out=special replace; delimiter="~"; getnames=yes; guessingrows=32767;/*maximum number of records*/ run; data aesi_special; length term_pt $120; set special; term_pt=strip(lowcase(term)); proc sort nodupkey; by term_pt; run; data aesi_special1(keep=start label fmtname); set aesi_special (rename=(term_pt=start)); retain fmtname 'aesi' type 'I'; label=1; run; proc format cntlin = aesi_special1; run;   attached screenshot of .csv file and datastep(aesi_special1) ... View more

Thank you. But probably i missed something else... or it works not correct: MPRINT(MM_TEMPL_AE_FULL): proc format cntlin = aesi_special; ERROR: This range is repeated, or values overlap: arteriovenous fistula site complication-arteriovenous fistula site complication. ... View more

Hi RW9,   So the dataset with full list of terms is large main dataset which should be reported. Another one created using proc import and in this one all interested terms which should be flaged in main big dataset: proc import datafile='/sas/common/dev/templates/progs/drt/medmon/ae_special_1.csv' dbms=dlm out=special replace; delimiter="~"; getnames=yes; guessingrows=32767;/*maximum number of records*/ run;   The problem how to mark terms in main big dataset if they are presented in 'special'. It is the main goal. Simple left join/merge of using 'if term in ("&terms")' also is not working... but terms could be flaged when 'proc format' used. So i thought that 'proc format' could be geberated using 'call execute' ...unfortunatelly i did not use it earlier so some things are unknown for me...  ... View more

mkeintz thank you! ... View more

Using 'having' in this code it works but gives the empty dataset =( proc sql; create table fix_ex1 as select a.treat, a.subject, a.date format datetime22.3, a.exstdt format datetime22.3, a.exrdi, a.date, a.rec_ds, a.dsdecod, max(vddt) as vdd_max format datetime22.3, b.foldername, b.folderseq, b.vdyn from fix_ex1_1 as a left join visits as b on a.subject=b.subject having calculated vdd_max between a.exstdt and a.date; *group by a.subject,b.folderseq,a.treat,a.date,a.exstdt,a.exrdi,a.date,a.rec_ds,a.dsdecod; quit; ... View more

tried to use different modifications of group by, order by but it is not works...   proc sql; create table fix_ex1 as  select  a.treat,  a.subject,  a.date,  a.exstdt,  a.exrdi,  a.date,  a.rec_ds,  a.dsdecod,  max(vddt) as vdd_max,  b.foldername,  b.folderseq,  b.vdyn  from fix_ex1_1 as a  left join visits as b on a.subject=b.subject  where calculated vdd_max between a.exstdt and a.date  group by a.subject,b.folderseq,a.treat,a.subject,a.date,a.exstdt,a.exrdi,a.date,a.rec_ds,a.dsdecod; ERROR: Summary functions are restricted to the SELECT and HAVING clauses only. ... View more

Thank you for response . But it is not helps. Or i missed something else?  proc sql; create table fix_ex1 as  select  a.treat,  a.subject,  a.date,  a.exstdt, a.exrdi,  a.date,  a.rec_ds,  a.dsdecod,  max(vddt) as vdd_max,  b.foldername,  b.folderseq,  b.vdyn  from fix_ex1_1 as a  left join visits as b on a.subject=b.subject  where calculated vdd_max between a.exstdt and a.date  order by a.subject,b.vdd_max,b.folderseq; ERROR: Column vdd_max could not be found in the table/view identified with the correlation name B. ERROR: Summary functions are restricted to the SELECT and HAVING clauses only. ... View more