topic Re: A question on using index as a macro function in SAS Programming

A question on using index as a macro function

Shayan2012 — Sun, 27 Jul 2014 17:56:25 GMT

Hi all,

I am a bit confused about using index function since I am not getting the desired result.

So, I have written a Macro, which first assigns a name to some macro variable, and then searches for that variable in a text, as the code below. My problem is that I get y=0 everytime that I search for the text, even though that I know that the desired text is in the source. Can some one please tell me what I am doing wrong?

Thanks a lot!

data list;

input target $;

cards;

BILL

JOE

;

run;

data source;

input line $;

cards;

TESTBILL

Aaa

JOEY

;

run;

%macro test;

%DO d=1 %TO 2 ;

data Listfile;

do d=&d;

set list point=d;

CALL SYMPUT("NAMEVAR",target);

output;

end;

stop;

run;

%put &NAMEVAR;

%do b=1 %to 3;

data crawler;

do b=&b;

set source point=b;

y=index(line,"&NAMEVAR");

end;

stop;

run;

%end;

%mend;

%test;

Re: Use macrofunction or not?

Peter_C — Sun, 27 Jul 2014 18:24:29 GMT

CALL SYMPUT() puts a value into a symbol

From where will your data step obtain its value in NAME?

Re: Use macrofunction or not?

Shayan2012 — Sun, 27 Jul 2014 18:42:52 GMT

I am sorry Peter. My question was submitted incomplete so I was editing it. I just edited that in the main question. So, I am trying to read the variable target from the list dataset and put it in the NAMEVAR macro variable.

Re: A question on using index as a macro function

Tom — Sun, 27 Jul 2014 22:10:43 GMT

Your program does not make any sense, but if you put this line after the line that is calculating Y you should be able to see that the trailing spaces you put into the macro variable by using CALL SYMPUT instead of CALL SYMPUTX is probably why you are not getting any hits.

put y= line= "NAMEVAR='&namevar'";

Re: A question on using index as a macro function

Tom — Sun, 27 Jul 2014 22:17:08 GMT

If you want to compare all lines to all targets you do not need to mess with macro coding.

data want ;

set source ;

do p=1 to nobs ;

set list point=p nobs=nobs ;

y=index(line,trim(target));

put (_n_ p line target y) (=) ;

output;

end;

run;

_N_=1 p=1 line=TESTBILL target=BILL y=5

_N_=1 p=2 line=TESTBILL target=JOE y=0

_N_=2 p=1 line=Aaa target=BILL y=0

_N_=2 p=2 line=Aaa target=JOE y=0

_N_=3 p=1 line=JOEY target=BILL y=0

_N_=3 p=2 line=JOEY target=JOE y=1

NOTE: There were 3 observations read from the data set WORK.SOURCE.

NOTE: The data set WORK.WANT has 6 observations and 3 variables.

Re: A question on using index as a macro function

wimaerts — Mon, 28 Jul 2014 14:09:41 GMT

It is good to comment on the macro code, to better understand how it works...

Use options mprint mlogic to better understand what is going on when the macro is executed...

First, you have to trim trailing spaces from the test string :

CALL SYMPUT ("NAMEVAR",trim(target));

The dataset crawler remains empty because there is nothing written to the dataset...

An output statement before the stop statement would write a line to the crawler dataset...

data crawler;

do b=&b;

set source point=b;

y=index(line,"&NAMEVAR");

end;

output;

stop;

run;

The datastep generated by the macro also is generated at every loop .... when executing one after the other, the same dataset is recreated at every datastep boundary.. so you would only see the last result ...

When for example using the counters in the dataset name :

data crawler&b&d;

a dataset is generated for every iteration..

showing the results....

Re: A question on using index as a macro function

Shayan2012 — Mon, 28 Jul 2014 20:02:01 GMT

Thanks a lot, Tom. Yeah, your code is definitely the correct way to do that and mine is messy. I guess I need to get more familiar with data step!

Re: A question on using index as a macro function

Shayan2012 — Mon, 28 Jul 2014 20:03:21 GMT

Thanks a lot wimaerts. Actually, I don't think that trim be the problem. Since I had already take care of spaces in the previous steps of my code. Thats why I thought I am misusing macro functions in some way.

Re: A question on using index as a macro function

Tom — Tue, 29 Jul 2014 00:24:17 GMT

SAS stores character variables as fixed length strings. So if variable target is of length $8 when you store the value 'BILL' into target it is actually stored with 4 trailing spaces to pad the field out to its full length. So when you use CALL SYMPUT('namevar',target) you are generating macro variable NAMEVAR with 4 extra spaces. If you use call symputX('namevar',target) then the trailing spaces are not put into the macro variable. Or you can do it yourself with the TRIM() function and continue to use CALL SYMPUT.

Re: A question on using index as a macro function

Scott_Mitchell — Tue, 29 Jul 2014 01:39:08 GMT

You could also use SQL for this task.

PROC SQL NOPRINT;

CREATE TABLE WANT1 AS

SELECT *,

INDEX(LINE,TRIM(TARGET)) AS POS

FROM LIST A,

SOURCE B

;

QUIT;