DATA Step, Macro, Functions and more

create proc format in macro using call execute

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

create proc format in macro using call execute

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 


Accepted Solutions
Solution
‎01-23-2017 04:03 AM
Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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


All Replies
Super User
Super User
Posts: 7,993

Re: create proc format in macro using call execute

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.

Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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
Super User
Super User
Posts: 7,993

Re: create proc format in macro using call execute

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;
SAS Super FREQ
Posts: 8,868

Re: create proc format in macro using call execute

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
PROC Star
Posts: 7,492

Re: create proc format in macro using call execute

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

 

Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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.

SAS Super FREQ
Posts: 8,868

Re: create proc format in macro using call execute

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
PROC Star
Posts: 7,492

Re: create proc format in macro using call execute

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

Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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
Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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
PROC Star
Posts: 7,492

Re: create proc format in macro using call execute

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

 

Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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
Super User
Posts: 19,869
Solution
‎01-23-2017 04:03 AM
Occasional Contributor
Posts: 14

Re: create proc format in macro using call execute

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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