BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jeka
Obsidian | Level 7

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Jeka
Obsidian | Level 7

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.

View solution in original post

14 REPLIES 14
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Jeka
Obsidian | Level 7

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... 


Capture_call.JPG
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Cynthia_sas
SAS Super FREQ
In addition, it almost looks like you are trying to build PROC FORMAT code from your data set. But there is already a way to use a data set to build formats directly, without writing the format code. All you have to do is build the right kind of data set with the right variables and then use PROC FORMAT with the CNTLIN= option. Here's a paper that describes the process http://www2.sas.com/proceedings/forum2007/068-2007.pdf and here's the documentation example: http://support.sas.com/documentation/cdl/en/proc/69850/HTML/default/viewer.htm#n1e19y6lrektafn1kj6nb...

You probably will not need any macro code or CALL EXECUTE to build a format from a SAS data set.

cynthia
art297
Opal | Level 21

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

 

Jeka
Obsidian | Level 7

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.

Cynthia_sas
SAS Super FREQ
Hi: Your log with MPRINT implies you are still doing this within a MACRO context. As Art297's example shows, you do NOT need to use Macro processing. The rule of thumb is to start with working code before you "macro-ize" the code.

Again, if you want anyone to help you, you need to post some examples of your data and a Non-macro example of using CNTLIN that generates an error.

cynthia
art297
Opal | Level 21

Sounds like some values in special are repeated more than once.

Jeka
Obsidian | Level 7

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)


aesi.JPGaesi_csv.JPG
Jeka
Obsidian | Level 7

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!


list_terms.JPG
art297
Opal | Level 21

It would be more helpful if you posted the csv file(s) rather than pictures of them

 

Jeka
Obsidian | Level 7

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


term_pt.JPG
Jeka
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 2052 views
  • 7 likes
  • 5 in conversation