topic Call symputx macro variable cannot be used in %if %then? in SAS Programming

Call symputx macro variable cannot be used in %if %then?

JohnChen_TW — Thu, 04 May 2017 08:34:26 GMT

%let var=x;
proc sql;
	create table detect as select type
	from Sashelp.Vcolumn
	where libname='WORK' and name="&Var.";
quit;
%if &fm='num' %then %do;
.
.
.
%end;

Above is a part of my macro program.

I am not sure why the condition (%if...%then %do) cannot work...

Re: Call symputx macro variable cannot be used in %if %then?

RW9 — Thu, 04 May 2017 08:44:24 GMT

For statements with a leading % these are macro statements and can only be used in a macro block, i.e.

%macro Tmp ();

  %if ...;

%mend Tmp;

%tmp;

These cannot be used in open Base SAS code. You could of course just do:

data _null_;
  set sashelp.vcolumn (where=(libname="WORK" and NAME="X"));
  if type="num" then call execute('%number_version;');
  else call execute('%character_version;');
run;

Just an example, depends verty much on what you are trying to do - which I can't see. Just bear in mind that macro is never needed.

Re: Call symputx macro variable cannot be used in %if %then?

Kurt_Bremser — Thu, 04 May 2017 09:00:32 GMT

There's absolutely nothing to say from this code snippet.

- is there a macro definition or not?

- where does &fm come from?

- what are you trying to do in the %do ... %end block?

As @RW9 already said, there's a close to 100% chance that what you want to do does not need a macro at all, so tell us what issue you are trying to solve.

Edit: PS Your subject mentions call symputx, but there's no call symput or symputx at all in your code.

Re: Call symputx macro variable cannot be used in %if %then?

JohnChen_TW — Thu, 04 May 2017 09:19:30 GMT

Sorry, please see the following full code.

%macro test(have,var);
proc sql;
create table detect as select type
from sashelp.column
where libname='WORK' and name="&var";
quit;

data _null_; set detect;
call symputx('ty',type);
run;

%if &ty='num' %then %do;
proc means data=&have;
var=&var;
run;
%end;

%mend;
%test(x,y);

Re: Call symputx macro variable cannot be used in %if %then?

RW9 — Thu, 04 May 2017 09:33:20 GMT

And what does dataset x look like, does it have a variable Y? What I suspect is happening is that the table detect is not getting any records based on WORK.X for variable Y and so the macro variable is not being created. Then its failing.

However this whole piece of code is not needed anyways (as macro is never needed):

proc means data=have;  
  var _numeric_;
run;

Will do all numeric variables in the dataset, you can filter that output by:

proc means data=have;  
  var _numeric_;
  output out=want;
run;
data want;
  set want;
  where ...;
run;

Re: Call symputx macro variable cannot be used in %if %then?

JohnChen_TW — Thu, 04 May 2017 10:19:55 GMT

Before creating table detect, there was a DATA PROCESS to put the external dataset in WORK named as X.

However, that will be time-consuming to check the variable types one by one when there are too many datasets, Also, when I put the dataset in WORK, sashelp.Vcolumn has recorded all types of the variables from each dataset.

So, I think I shoud do PROC SQL to get the type of the variable and save it as a macro variable first, and then do PROC MEANS/PROC FREQ under the condition as %if (type is num/char) %then...%do.

Re: Call symputx macro variable cannot be used in %if %then?

RW9 — Thu, 04 May 2017 10:31:11 GMT

Sorry, one of us is misunderstandingsomething here. Your problem is caused by your dataset not being present, or not having a variable Y, whichever code method you use is not going to change that. As for the code I presented, it works on all numeric variables, you don't need to check the type beforehand which is what your doing now. If you only want one variable, then retain that:

proc means data=have (keep=y);  
  var _numeric_;
  output out=want;
run;

Is there any value in adding lots of extra code?

Re: Call symputx macro variable cannot be used in %if %then?

Astounding — Thu, 04 May 2017 10:43:17 GMT

Your comparison is false:

%if &ty='num' %then %do;

Macro variables do not require quotes to make comparisons. You are actually comparing &ty to 5 characters (including the quotes), not comparing it to 3 characters. The correct comparison would be:

%if &ty=num %then %do;

Re: Call symputx macro variable cannot be used in %if %then?

Kurt_Bremser — Thu, 04 May 2017 10:43:38 GMT

Let's look at a small piece:

data _null_; set detect;
call symputx('ty',type);
run;

%if &ty='num' %then %do;

Here you have to keep in mind that the macro language knows only one datatype, namely text. Therefore quotes around text are not needed; if you use them, they will become part of the value.

Macro variable ty will therefore contain num or char (without quotes), as assigned in the preceding data step. The comparison with 'num' will therefore always fail. 'num' ne num.

To make your code (which I hope is for learning only, doing that in real programming would constitute a major programming crime) workable, adapt it like that:

data class;
set sashelp.class;
run;

%macro test(have,var);

%let ty=x; /* create ty with dummy value to avoid error message */

proc sql noprint;
select type into :ty
from dictionary.columns
where libname = 'WORK' and upcase(memname) = upcase("&have") and upcase(name) = upcase("&var");
quit;

%if &ty = num %then %do;
proc means data=&have;
var &var;
run;
%end;
%else %put Variable not found or variable not numeric;
%mend;

%test(class,age);
%test(class,name);

Re: Call symputx macro variable cannot be used in %if %then?

JohnChen_TW — Thu, 04 May 2017 11:28:59 GMT

Yes, as @Astounding said, the program works if removed the quotes.

Many thanks for your explanation and provide.
You have added to my knowledge.

But why "%let ty=x;" can avoid error message? what kind of error message?

JC

Re: Call symputx macro variable cannot be used in %if %then?

Kurt_Bremser — Thu, 04 May 2017 11:33:10 GMT

If the proc sql finds no matching observations, macro variable ty will not be created in the SQL step. You will then get a WARNING when you use &ty later on. Setting a default value prevents the WARNING.

See Maxims 24&25.

Re: Call symputx macro variable cannot be used in %if %then?

JohnChen_TW — Fri, 05 May 2017 07:22:36 GMT

Thanks for your suggestion, it works.

Re: Call symputx macro variable cannot be used in %if %then?

JohnChen_TW — Fri, 05 May 2017 07:24:25 GMT

Okay, thanks for your information!
Also thanks for all your help!! (@RW9, @Astounding)

JC