DATA Step, Macro, Functions and more

%scan function with

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

%scan function with

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+

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

        

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


Accepted Solutions
Solution
‎02-18-2015 06:29 AM
Respected Advisor
Posts: 3,799

Re: %scan function with

%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

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: %scan function with

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.

Contributor
Posts: 54

Re: %scan function with

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

Biochemistry Comment 2       good     . 

Biochemistry Comment 2       ok       .

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

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

Biochemistry Comment 2       good     . 

Biochemistry Comment 2       ok       .

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

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

serum                        8        .

serum                        9        9

;

run;

Best,

skp

Super User
Super User
Posts: 7,997

Re: %scan function with

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

Biochemistry Comment 2       good     .

Biochemistry Comment 2       ok       .

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

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.

Contributor
Posts: 54

Re: %scan function with

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

Super User
Super User
Posts: 7,083

Re: %scan function with

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. 
Contributor
Posts: 54

Re: %scan function with

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

Solution
‎02-18-2015 06:29 AM
Respected Advisor
Posts: 3,799

Re: %scan function with

%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
Contributor
Posts: 54

Re: %scan function with

Posted in reply to data_null__

Thanks Data_nul ! I'will implement it.

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

Cheers

skp

Respected Advisor
Posts: 3,799

Re: %scan function with

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

Contributor
Posts: 54

Re: %scan function with

Posted in reply to data_null__

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

Respected Advisor
Posts: 3,799

Re: %scan function with

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 473 views
  • 4 likes
  • 4 in conversation