topic Re: Macro variable with hyphen used in scan function in SAS Programming

Macro variable with hyphen used in scan function

akj — Wed, 22 Nov 2017 19:30:55 GMT

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;

Re: Macro variable with hyphen used in scan function

Reeza — Wed, 22 Nov 2017 19:46:54 GMT

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

Re: Macro variable with hyphen used in scan function

SuryaKiran — Wed, 22 Nov 2017 19:52:37 GMT

Hi,

In your proc step SEPARATED is missing

Re: Macro variable with hyphen used in scan function

Tom — Wed, 22 Nov 2017 20:21:44 GMT

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,|)

Re: Macro variable with hyphen used in scan function

SuryaKiran — Wed, 22 Nov 2017 20:10:10 GMT

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;

Re: Macro variable with hyphen used in scan function

akj — Wed, 22 Nov 2017 20:40:17 GMT

Thanks. It got lost in the transcription.

Re: Macro variable with hyphen used in scan function

akj — Wed, 22 Nov 2017 20:43:24 GMT

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"

Re: Macro variable with hyphen used in scan function

akj — Wed, 22 Nov 2017 20:44:32 GMT

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

Re: Macro variable with hyphen used in scan function

Reeza — Wed, 22 Nov 2017 21:00:52 GMT

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

Re: Macro variable with hyphen used in scan function

Tom — Wed, 22 Nov 2017 21:34:06 GMT

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;

Re: Macro variable with hyphen used in scan function

Tom — Wed, 22 Nov 2017 21:35:39 GMT

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?

Re: Macro variable with hyphen used in scan function

Tom — Wed, 22 Nov 2017 21:43:15 GMT

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;

Re: Macro variable with hyphen used in scan function

akj — Wed, 22 Nov 2017 23:19:19 GMT

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