Hi All, could someone please help me to understand is it possible or is there any another solution for this case?
I have .csv file with terms which should be flagged. I tried to make it using merge/left join but it is not working correctly inside the macro i would say it is not working at all. I suppose that this could be resolved using proc format(this was checked and really working with 'invalue' strange thing but using value and character type of format does not gives any result ).Then i tried to use 'call execute' but probably there is thing which i missed or maybe i don't know something.
Here is my idea:
proc import datafile='/sas/bla/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=cats(' " ',lowcase(strip(term)),' " ','=','1'); /* values like: "pruritus"=1 */
proc sort nodupkey;
by term_pt;
run;
/* then i gethering all values into &_aesi using sql */
proc sql noprint;
select distinct term_pt into: _aesi separated by ' '
from aesi_special;
quit;
/*here is the main problem...as it gives an error and format is not recognized in further step is there any other way to resolve this?*/
data _null_;
call execute(
'proc format; invalue aesi '
|| '%put &_aesi'
|| ' ;'
|| ' run;');
run;
ERROR:
NOTE: Line generated by the CALL EXECUTE routine.
1 + invalue aesic run;
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: (, ',', -, <, =.
ERROR 76-322: Syntax error, statement will be ignored.
Thank you
Thank you for advise. I found another solution : compress(strip(aeterm_pt),,'ak') using this the flag was simply merged from one to another dataset. It did not work as there some additional symbols differs in .csv files which created under windows for .csv unix format.
Please post test data, in the form of a datastep, with your input data. And what you want out. You should be able to call execute the necessary code straight off without need for formats and such like.
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...
Well, sorry, if you can't provide anything to work with, I can't provide working code. Look at other posts on the forums on how to post test data and example output. At a guess:
data _null_; set list_of_terms_dataset end=last; if _n_=1 then call execute('data want; set have;'); call execute('if index(thestring,"'||strip(words)||'") then special_flag=1;'); if last then call execute('run;'); run;
I agree, some example data and what you want would definitely help. Your current code, once corrected, would return 1s for all included values.
However, if it turns out that you do need to create an informat, you can do it without needing call execute. e.g.:
data special;
input term $;
cards;
aaa
bbb
ccc
ddd
eee
;
data aesi_special;
set special (rename=(term=start));
retain fmtname 'aesi' type 'I';
label=1;
run;
proc format cntlin = aesi_special;
run;
HTH,
Art, CEO, AnalystFinder.com
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.
Sounds like some values in special are repeated more than once.
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)
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!
It would be more helpful if you posted the csv file(s) rather than pictures of them
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
Thank you for advise. I found another solution : compress(strip(aeterm_pt),,'ak') using this the flag was simply merged from one to another dataset. It did not work as there some additional symbols differs in .csv files which created under windows for .csv unix format.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.