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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.