Macro variable with hyphen used in scan function

[akj]

I have a macro variable that contains hyphens and which are not correctly read by the scan function.  The scan function treats the hyphens as delimiters.  How do I stop this?  My guess is that it is a modifier in the scan function, but which one?

rx_list looks like this: ACE ACE-ARB ACE-DIU ARB ARB-ACE-DIU ARB-DIU

instead of reading it in as 6 different values, it creates 11 in the following macro.

proc sql noprint;


  select distinct name into : rx_list by ' ' from orig_data;


quit;





%macro rx_pre;


  %local I next_rx;


  %let I=1;


  %do %while (%scan(rx_list, &i) ne);


    %let next_rx=%scan(&rx_list, &i);


       ... data step ...


    %let i=&eval(&i+1);


  %end;


%mend;





%rx_pre;

[Reeza]

The default delimiter in SCAN is spaces. You can specify custom delimiters using the third parameter option.

[SuryaKiran]

Hi,

In your proc step SEPARATED is missing

[akj]

Thanks. It got lost in the transcription.

[Tom · Accepted solution]

Hyphen is one of the many default delimiters that %SCAN() will use if you do not give it the delimiter you want to use.

Tell %SCAN() that you only want to use space as the delimiter.

%do %while (%scan(rx_list, &i,%str( )) ne);

Or use some other delimiter.

...
into :rx_list separated by '|' 
...
%scan(&rx_list,&i,|)

[akj]

I tried the '|' and the first iteration of the macro works fine, but it gets hung up on the second with an error message: "A character operand was found in the %eval function or %IF condition where a numeric operand is required. The condition was %scan(&rx_list, &i, '|') ne"

[Tom]

Why did you include quotes in the list of delimiters you passed to the %SCAN() function?  Do you actually have quote characters in your data?

[akj]

No, I don't and removing them seems to have solved the issue.  Thank you.

[SuryaKiran]

Hi,

Try something like this:

data have;
infile datalines;
input name $20.;
datalines;
ACE
ACE-ARB
ACE-DIU
ARB
ARB-ACE-DIU
ARB-DIU
;
run;

proc sql ;
select name,count(*) into : rx_list separated by "|" ,:count
from have;
quit;

%macro rx_pre;
DATA test;
%do i=1 %to &count;
%let next_rx=%scan(&rx_list,&i,"|");
Var_&i="&next_rx.";
%end;
RUN;
%mend;
%rx_pre;

[akj]

Thank you for the suggestion, but I am getting the values from a dataset.

[Reeza]

@akj The first step is in the solution to create fake data to demonstrate how the code works. It’s known as a fully worked example/reproducible example. 

We don’t have your data or code to work with, so we can either make suggestions or make demo data to work with. Personally, I won’t take the time to make demo data anymore, but @SuryaKiran has take the time to make demo data to demonstrate a solution to your problem. 

[Tom]

Why did you include quotes in the list of delimiters you passed to the %SCAN() function?

%macro rx_pre;
DATA test;
%do i=1 %to &count;
%let next_rx=%scan(&rx_list,&i,|);
 Var_&i="&next_rx.";
%end;
RUN;
%mend;

[Tom]

Make sure to use the right delimiter list. It is easier to use an iterative do loop.

%let rx_list=ACE ACE-ARB ACE-DIU ARB ARB-ACE-DIU ARB-DIU;
%do i=1 %to %sysfunc(countw(&rx_list,%str( ));
   %let next=%scan(&rx_list,&i,%str( ));
%end;