topic Re: %scan function with in SAS Programming

%scan function with

saskapa — Wed, 18 Feb 2015 09:47:47 GMT

Hi,

While I was using a %scan function it apperas that one of the values of my macro variables have strings that causes problems. The message I have is :

"ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand"

Here is a log excerpt with all the values of the macro variable that code has to scan through :

I highlighted in red where the issue occurs

SYMBOLGEN: Macro variable TEST resolves to Biochemistry Comment 1|Biochemistry Comment 2|Blinded

19+CD20-IgD-CD27+CD38hi %(CD19)|Blinded 19+IgD+CD27+ %(CD19)|Blinded 19+IgD+CD27-

%(CD19)|Blinded 19+IgD-CD27+ %(CD19)|Blinded 19+IgD-CD27- %(CD19)|Blinded CD19+ %|Blinded

CD19+ ABS|Blinded CD19+CD20-IgD-CD27+CD38hi ABS|Blinded CD19+IgD+CD27+ ABS|Blinded

CD19+IgD+CD27- ABS|Blinded CD19+IgD-CD27+ ABS|Blinded CD19+IgD-CD27- ABS|Blinded CD3+

CD3+CD8+ ABS|Blinded CD4+CD45RA+CD197+ %(CD4)|Blinded CD4+CD45RA+CD197+ ABS|Blinded

CD4+CD45RA-CD183+CD196+ %(CD4)|Blinded CD4+CD45RA-CD183+CD196+ ABS|Blinded

CD4+CD45RA-CD183+CD196- %(CD4)|Blinded CD4+CD45RA-CD183+CD196- ABS

SYMBOLGEN: Macro variable NUM resolves to 3

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand

is required. The condition was: %scan(&test,&num.,'|') ne

ERROR: The condition in the %DO %WHILE loop, , yielded an invalid or missing value, . The macro will

stop executing.

here is the code that i use :

%do %while (%scan(&test,&num.,'|') ne %str()) ;

%let test_&dts.=%scan(&test),&num.,'|');

So because of the minus sign , I though that I should tell SAS to not interpret it as minus sign and to do so I used the %str() function , but it doesn't seems to work

%do %while (%scan(&test,&num.,'|') ne %str()) ;

%let test_&dts.=%scan(%str(&test),&num.,'|');

Any idea why and how this issue occurs and and how to avoid the error ?

Regards

skp

Re: %scan function with

RW9 — Wed, 18 Feb 2015 10:12:39 GMT

In my opinion your problems lie in using macro variables in such a way. There are many other posts on this forum where lists of information is described. Whilst its "possible" to do it this way the code very quickly becomes unreadable and complicated. In your example, which I am not going to look at closely, you have all kinds of special characters and such like, any of which may be causing the problems. Maybe give an explanation of what you are trying to do with test data and required output.

Re: %scan function with

data_null__ — Wed, 18 Feb 2015 11:29:33 GMT

%STR won't do it. I would write the scan like this using %SUPERQ to quote the value of TEST and use %QSCAN to quote the result.. Notice I added a semicolon and comma. Don't use quotes around the delimiter in SCAN function if you need to quote delimiter use %STR

Also notice I only SCAN one time and test W is null in %DO.

data _null_; 
   x = 'Biochemistry Comment 1|Biochemistry Comment 2|Blinded
19+CD20-IgD-CD27+CD38hi %(CD19)|Blinded 19+IgD+CD27+ %(CD19)|Blinded 19+IgD+CD27-
%(CD19)|Blinded 19+IgD-CD27+ %(CD19)|Blinded 19+IgD-CD27- %(CD19)|Blinded CD19+ %|Blinded
CD19+ ABS|Blinded CD19+CD20-IgD-CD27+CD38hi  ABS|Blinded CD19+IgD+CD27+ ABS|Blinded
CD19+IgD+CD27-  ABS|Blinded CD19+IgD-CD27+ ABS|Blinded CD19+IgD-CD27- ABS|Blinded CD3+
%|Blinded CD3+ ABS|Blinded CD3+CD4+ %|Blinded CD3+CD4+ ABS|Blinded CD3+;CD8+ %|Blinded
CD3+CD8+ ABS|Blinded CD4+CD45RA+CD197+ %(CD4)|Blinded CD4+CD45RA+CD197+ ABS|Blinded
CD4+CD45RA-CD183+CD196+ %(CD4)|Blinded, CD4+CD45RA-CD183+CD196+ ABS|Blinded
CD4+CD45RA-CD183+CD196- %(CD4)|Blinded CD4+CD45RA-CD183+CD196- ABS'; 

   call symputx('test',x,'g');
   run; 

%macro scanit; 
   %local i w;
   %let i = %eval(&i + 1);
   %let w = %qscan(%superq(test),&i,|);
   %do %while(%superq(w) ne);
      %put NOTE: %nrbquote(&=w); 
      %let i = %eval(&i + 1);
      %let w = %qscan(%superq(test),&i,|);
      %end; 
   %mend scanit;
%scanit; 
    

56         %scanit; 
NOTE: W=Biochemistry Comment 1
NOTE: W=Biochemistry Comment 2
NOTE: W=Blinded19+CD20-IgD-CD27+CD38hi %(CD19)
NOTE: W=Blinded 19+IgD+CD27+ %(CD19)
NOTE: W=Blinded 19+IgD+CD27-%(CD19)
NOTE: W=Blinded 19+IgD-CD27+ %(CD19)
NOTE: W=Blinded 19+IgD-CD27- %(CD19)
NOTE: W=Blinded CD19+ %
NOTE: W=BlindedCD19+ ABS
NOTE: W=Blinded CD19+CD20-IgD-CD27+CD38hi  ABS
NOTE: W=Blinded CD19+IgD+CD27+ ABS
NOTE: W=BlindedCD19+IgD+CD27-  ABS
NOTE: W=Blinded CD19+IgD-CD27+ ABS
NOTE: W=Blinded CD19+IgD-CD27- ABS
NOTE: W=Blinded CD3+%
NOTE: W=Blinded CD3+ ABS
NOTE: W=Blinded CD3+CD4+ %
NOTE: W=Blinded CD3+CD4+ ABS
NOTE: W=Blinded CD3+;CD8+ %
NOTE: W=BlindedCD3+CD8+ ABS
NOTE: W=Blinded CD4+CD45RA+CD197+ %(CD4)
NOTE: W=Blinded CD4+CD45RA+CD197+ ABS
NOTE: W=BlindedCD4+CD45RA-CD183+CD196+ %(CD4)
NOTE: W=Blinded, CD4+CD45RA-CD183+CD196+ ABS
NOTE: W=BlindedCD4+CD45RA-CD183+CD196- %(CD4)
NOTE: W=Blinded CD4+CD45RA-CD183+CD196- ABS

Re: %scan function with

saskapa — Wed, 18 Feb 2015 12:54:57 GMT

Hi RW9,

Thanks for your response.

There are indeed special characters. What I need is to mask those special characters. Despite using %nrstr,%nrquote it doesn't work.

The fact that I have special character shouldn't prevents me to change of coding strategy. Because

1 /SAS provides function to avoid such issues and I want to make use of it.

2/ I am in a learning process and coding by following our intuitions just because form the mistakes that we are doing we can learn from it.

My code is related to SDTM. In domain where applicable( lets's use LB), LBSTRESN (result in numeric type) is missing while LBORRES (result in character type) is not.This scenario is possible, in case for instance that LBORRES=<6 , in this case LBSTRESN is null. Or LBORRES=BLINDED then in this case LBSTRESN is null.

My goal is to create a report that picks up cases where LBSTRESN is null and LBORRES contains only digit, which means then that LBSTRESN should not be missing. I achieve this by comparing the count of LBORRES that do not contains a digit at first position ( in fact it is not true because '.6' does not contain a digit at first position but could be converted in numeric but lets say this for the moment..) Those count will be compared to my non digit cases computed in proc summary.

Here is an dataset example:

data lb ;

infile cards dsd missover;

input lbtest $1-28 lborres $ 30-37 lbstresn ;

cards;

Biochemistry Comment 1 ok .

Biochemistry Comment 2 good .

Biochemistry Comment 2 ok .

Blinded 19-IgD+CD27+ %(CD19) blinded .

serum 8 .

serum 9 9

;

run;

*Method;(this section is only to explain my code, find below my full macro)

*-------------------------------------------------------------------------------------------------------------;

**Compute a summary by test in LB;

*-------------------------------------------------------;

proc summary data=&dts._ ;

var &dts.stresn;

by &dts.test;

output out=summary_&dts.;

run;

*Get a list of test for which lbstresn=0, in other word get lbtest for which numerical result is null.

proc sql;

select &dts.test into :test separated by '|'

from summary_&dts.

where &dts.stresn eq 0 and _stat_ eq 'N';

quit;

and perform for each of those test a frequency test :

%let num=1;

%do %while (%scan(&test,&num.,'|') ne %str( )) ;

%let test_&dts.=%scan(&test,&num.,'|');

%put &&test_&dts ;

proc freq data=&dts. ;

tables &dts.orres/out=&dts._&num.(drop=percent);

where &dts.test="&&test_&dts.";

run;

data &dts._test_&num.; set &dts._&num.;

length domain $20. test $50.;

domain="&dts.";

test="&&test_&dts.";

rename &dts.orres=test_orres;

keep domain test &dts.orres &dts.orres count ;

run;

%let num=%eval(&num.+1);

%end;

*Macro call;

*------------------;

option mprint mlogic symbolgen;

%orres_strsn(lib=work,dts=lb)

**The full macro is here :

*-----------------------------------;

%macro orres_strsn (lib=,dts=);

proc sort data=&dts. out=&dts._;

by &dts.test;

run;

proc summary data=&dts._ ;

var &dts.stresn;

by &dts.test;

output out=summary_&dts.;

run;

proc sql;

select &dts.test into :test separated by '|'

from summary_&dts.

where &dts.stresn eq 0 and _stat_ eq 'N';/*only the tests where lbstresn is null*/

quit;

%put &test.;

%let num=1;

%do %while (%scan(&test,&num.,'|') ne %str()) ;

%let test_&dts.=%scan(%nrstr(&test),&num.,'|');

%put &&test_&dts ;

proc freq data=&dts. ;

tables &dts.orres/out=&dts._&num.(drop=percent);

where &dts.test="&&test_&dts.";

run;

data &dts._test_&num.; set &dts._&num.;

length domain $20. test $50.;

domain="&dts.";

test="&&test_&dts.";

rename &dts.orres=test_orres;

keep domain test &dts.orres &dts.orres count ;

run;

%let num=%eval(&num.+1);

%end;

data orres_; set &dts._test_1

%if &num. gt 2 %then %do;

%do i=2 %to %eval(&num-1);

&dts._test_&i.

%end; ;

%end;

by lbtest;

retain sum_cases 0;

nodigit=prxparse("/\d/");

position=prxmatch(nodigit,lborres);

if position ne 1 then sum_cases+1;

if last.lbtest then do;

if sum_cases ne 0 then output;

sum_cases=0;

end;

label sum_cases='Number of non digit cases';

keep lbtest sum_cases;

run;

proc sql;

create table diff_&dts._ as

select a.&dts.test,

a._freq_- a.&dts.stresn as cases_orres 'Number of non digit cases from proc summary',

b.sum_cases

from summary_&dts. as a left join orres_&count. as b

on a.&dts.test=b.&dts.test

where a.&dts.stresn eq 0; /*only on tests where lbstresn is null*/

quit;

%mend orres_strsn;

data lb ;

infile cards dsd missover;

input lbtest $1-28 lborres $ 30-37 lbstresn ;

cards;

Biochemistry Comment 1 ok .

Biochemistry Comment 2 good .

Biochemistry Comment 2 ok .

Blinded 19-IgD+CD27+ %(CD19) blinded .

serum 8 .

serum 9 9

;

run;

Best,

skp

Re: %scan function with

saskapa — Wed, 18 Feb 2015 12:58:00 GMT

Thanks Data_nul ! I'will implement it.

I am surprise to it takes so much code to just mask '-' and '%'...:smileyconfused:

Cheers

skp

Re: %scan function with

data_null__ — Wed, 18 Feb 2015 13:02:03 GMT

What do you mean so much code? In macro language quoting is done with functions. You can't change that.

Re: %scan function with

RW9 — Wed, 18 Feb 2015 13:09:48 GMT

Well, if I understand your logic correctly, the following code will add a flag which highlights the rows where LBSTRESN should be populated but isn't:

data lb ;

infile cards dsd missover;

input lbtest $1-28 lborres $ 30-37 lbstresn ;

cards;

Biochemistry Comment 1 ok .

Biochemistry Comment 2 good .

Biochemistry Comment 2 ok .

Blinded 19-IgD+CD27+ %(CD19) blinded .

serum 8 .

serum 9 9

;

run;

proc sql;

create table WORK.LB_WANT as

select *,

case when LBSTRESN is null and anyalpha(LBORRES)=0 then "Y"

else "" end as FLAG

from WORK.LB;

quit;

You can then pull those rows out with a where clause if you want to print them.

Re: %scan function with

saskapa — Wed, 18 Feb 2015 13:12:16 GMT

Ok. Well I thought that innocently %nrquote would be enough....As it is written in the documentation below (see link SAS support on %nrquote 😞

SAS(R) 9.2 Macro Language: Reference

In my example : Blinded19+CD20-IgD-CD27+CD38hi %(CD19), we have '+','-','%','(' and ')'. Why %nrquote is not enough ?

skp

Re: %scan function with

data_null__ — Wed, 18 Feb 2015 13:18:54 GMT

NRQUOTE might be adequate but you weren't using it. You were using %STR.

Re: %scan function with

Tom — Wed, 18 Feb 2015 14:08:22 GMT

Your problem description does not include anything that makes it sound like the use of macro variables is needed (or desired).

My goal is to create a report that picks up cases where LBSTRESN is null and LBORRES contains only digit, which means then that LBSTRESN should not be missing.

Re: %scan function with

saskapa — Thu, 05 Mar 2015 14:56:09 GMT

Hi RW9,

Yes indeed, it is also possible to do so. However, I wanted to experiment parsing function and playing with quoting function within a sas macro program. That is why I choosed to write my code like this....

Cheers

skp

Re: %scan function with

saskapa — Thu, 05 Mar 2015 15:00:43 GMT

Hi Tom,

Yes indeed, as RW9's answer, the use of a macro variable is not be needed and a solution can be achieved easily with proc sql. However, I have done it for didactic reason ( learning macro codes and experience different use of quoting function in a macro environment ).

Best

skp