how to find macro variable value created by proc sql

[knveraraju91]

Dear,

In the below program how to find the value in the macro variable '&col" created by proc sql.     why this statement 

if index(&col, "A") eq 0 then do;

 in  the below data step giving me errors.   If I use the same data  step in the macro i am getting missing values for variable "A" .  Please suggest how to avoid "A"  variable values not to set missing. Thank you.

data one;
input a $ b $ c;
datalines;
a b 1
c d 2
;

proc sql noprint;

   select distinct(name) into: col separated by "$" from sashelp.vcolumn where libname="WORK" and memname="ONE";
quit;

data one;
set one;
if index(&col, "A") eq 0 then do;
A=" ";
end;
run; 


%macro col;
data one;
set one;
%if %index(&col, "A") eq 0 %then %do;
A=" ";
%end;
run;
%mend;
%col;

[Kurt_Bremser]

Use double quotes around &col.

[Tom]

The value of the macro variable just replaces the reference and the resulting program statement is then evaluated.

So you made COL have a value like A$B$C.

If you substitute that into your IF and %IF statements you get:

if index(A$B$C, "A") eq 0 then do;
%if %index(A$B$C, "A") eq 0 %then %do;

So the IF statement is not valid since you cannot have a variable name with dollar signs in it.  And your data step only has the variables A,B and C anyway.  If you want to treat that as a character constant then your need to enclose it in quotes.  But note that  you  get false positives if some other name contains A as part of the name.  So you should use the INDEXW() function instead.

if indexw("A$B$C", "A","$") eq 0 then do;
if indexw("&col", "A","$") eq 0 then do;

The macro %IF condition can never be true since your string, A$B$C does not contain the letter A with quotes around it. You could remove the quotes from around A, but then you might get false positives if some other name contains A as part of the name.

If you really want to search for the name A in the list of names you could use something like this instead.

%if %index($A$B$C$, $A$) eq 0 %then %do;
%if %index($&col$, $A$) eq 0 %then %do;