BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JohnChen_TW
Quartz | Level 8

 

%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...

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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);

View solution in original post

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

JohnChen_TW
Quartz | Level 8

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);

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

JohnChen_TW
Quartz | Level 8

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.

 

JC

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Astounding
PROC Star

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;

JohnChen_TW
Quartz | Level 8
Thanks for your suggestion, it works.
Kurt_Bremser
Super User

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);
JohnChen_TW
Quartz | Level 8
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
Kurt_Bremser
Super User

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.

JohnChen_TW
Quartz | Level 8
Okay, thanks for your information!
Also thanks for all your help!! (@RW9, @Astounding)

JC

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1712 views
  • 0 likes
  • 4 in conversation