topic how to find macro variable value created by proc sql in SAS Programming

how to find macro variable value created by proc sql

knveraraju91 — Sun, 07 Oct 2018 07:10:03 GMT

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;

Re: how to find macro variable value created by proc sql

Kurt_Bremser — Sun, 07 Oct 2018 07:20:53 GMT

Use double quotes around &col.

Re: how to find macro variable value created by proc sql

Tom — Sun, 07 Oct 2018 21:54:49 GMT

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;