SAS Macro Language 1: Essentials (UPDATED)

[BiFrost]

The demo I watched at the end of this course builds out a Macro to extract distinct values from columns and separate them into different tables. It allows for missing, number and character values in terms of table names. However, I keep receiving the same error message when testing:

Required operator nor found in expression %varexist (&tab,&col)=0. I have check this piece of code back to the demo and it is verbatim. Where am I going wrong?

options mcompilenote=all;

%macro splittable (tab,col);

options sasautos=("&path/autocall" sasautos);

/* Ensure parameter values are uppercase */
%let tab=%upcase(&tab);
%let col=%upcase(&col);

/* If only a table name is provided, add prefix WORK. */
%if %scan(&tab,2)=%then %do;
%let tab=WORK.&tab;
%end;

/* Check if the table exists */

%if %sysfunc(exist(&tab))=0 %then %do;
%put ERROR: &tab does not exist.;
%put ERROR: Macro will stop executing.;
%return;
%end;

/* Check if the column exists in the selected table */
%else %if %varexist(&tab,&col)=0 %then %do;
proc sql noprint;
select Name
into :varlist separated by ", "
from dictionary.columns
where libname="%scan(&tab,1)" and
memname="%scan(&tab,2)";
quit;
%put ERROR: Column &col does not exist in &tab..;
%put ERROR- Columns in &tab include &varlist..;
%put ERROR- Macro will stop executing.;
%return;
%end;

/* Create macro variable if COL is numeric */

%else %if %vartype(&tab,&col)=N %then %do;
proc sql; no print;
select distinct &col, cats("&col._",&col)
into:val1-, :table1-
from &tab
where &col is not missing;
quit;
%end;

/* Create macro variables if COL is character */

%else %if %vartype(&tab,&col)=C %then %do;
proc sql noprint;
select distinct &col format= $quote34.,
compress(&col,, 'nk')
into:val1-, :table1-
from &tab
where &col is not missing;
quit;
%end;

/* Build DATA step */

data
%do i=1 %to &sqlobs;
&&table&i
%end;;

set &tab;
select(&col);
%do i=1 %to &sqlobs;
when (&&val&i) output &&table&i;
%end;
otherwise;
end;
run;
%mend splittable;

options mprint;

/* (1) Table does not exist */
%splittable (false, ID)

/* (2) Column does not exist */
%splittable (sashelp.cars, Test)

/* (3) Column includes values with invalid characters */
%splittable (sashelp.cars, origin)

[ed_sas_member]

Hi @BiFrost 

It seems that there is a missing comma in the following statement :

options sasautos=("&path/autocall" sasautos);

It should be:

options sasauto=("&path/autocall",sasautos);

-> If you have defined a folder named "AUTOCALL" and specified the value of the macrovariable PATH in a %LET statement, SAS will normally be able to find the varexist.sas program in this location.

Best,

[Tom]

SAS doesn't need commas there. Just like it doesn't need commas in the lists you give to the IN operator.

[Tom]

You haven't showed the definition for %VAREXIST() or %VARTYPE() macros.

You haven't showed the definition of PATH macro variable.

Does the folder &path/autocall even exist?

Does it contain files named varexits.sas and vartype.sas (note that all of the letters in the the filenames need to be lowercase on a Unix filesystem.)

[ed_sas_member]

There are also some misspelling:

proc sql; no print; -> proc sql; noprint;

[BiFrost]

Thank you for all your contributions.

The macro is executing with no errors, as the spelling is close enough.

What are the extra lines of code I need, as I’m new to SAS?

[ed_sas_member]

It seems also that there are mistakes in auto call macros:

varexist.sas

vartype.sas  

[BiFrost]

This is the piece of code that is producing the error:

/* Check if the column exists in the selected table */

%else %if %varexist(&tab,&col)=0 %then %do;
proc sql noprint;
select Name
into :varlist separated by ", "
from dictionary.columns
where libname="%scan(&tab,1)" and
memname="%scan(&tab,2)";
quit;
%put ERROR: Column &col does not exist in &tab..;
%put ERROR- Columns in &tab include &varlist..;
%put ERROR- Macro will stop executing.;
%return;
%end;

[ed_sas_member]

Hi @BiFrost 

Please try to remove the semicolon at the line before %mend in the vartype.sas program:

 Best,

[Tom]

---

@ed_sas_member wrote:

It seems also that there are mistakes in auto call macros:

varexist.sas

vartype.sas  

---

Yes. The VARTYPE definition does NOT want that semi-colon after &VAL.  That will mess up your %IF statements by inserting a semi-colon before the %THEN part of the statement.

You can work around it by adding some quotes to your code.  While you are doing that fix the PROC SQL statement in the N branch.

...
%else %if "%vartype(&tab,&col)"="N;" %then %do;
proc sql noprint;
...
%else %if "%vartype(&tab,&col)"="C;" %then %do;
...

PS  You don't really need two macros.  Just use the %VAREXIST() macro that Tom Hoffman wrote over 21 years ago. 

https://github.com/sasutils/macros/blob/master/varexist.sas  

%if N=%varexist(&tab,&col,type) %then %do;
* Numeric variable exists ;
...
%end;
%else %if C=%varexist(&tab,&col,type) %then %do;
* Character variable exists ;
...
%end;
%else %do;
* Variable does not exist ;
...
%end;

[BiFrost]

Thank you for all the replies.

I think my issue is creating the autocall library in the first instance. Not sure exactly how that works? It is a fileref based on my computer drive?

[Tom]

---

@BiFrost wrote:
Thank you for all the replies.

I think my issue is creating the autocall library in the first instance. Not sure exactly how that works? It is a fileref based on my computer drive?

---

The SASAUTOS option takes a list of quoted physical directory names or unquoted fileref values.  So the example in your code is using one of each.

options sasautos=("&path/autocall" sasautos);

The SASAUTOS file ref should have been automatically defined when your SAS session started. It should point to where SAS can find the macros that come with the product.

The first directory is the one you need to get right.  It is using a macro variable named PATH to specify the higher level directory name and that appending /autocall to that. So it is looking for a subdirectory named autocall in the directory that PATH names.

The directory has to exist on the machine were SAS is running.  If you are using a server then on that server.  If you are using SAS University Edition then it needs to be a path that the virtual machine where SAS is running can see.  So it will have to be in the directory you shared with the virtual machine when you set it up.  And from SAS's point of view the directory name must start with /folders/myfolders as that is how SAS university edition works.

To test if PATH is set right you could try using %INCLUDE to compile out of those two macros.

%include "&path/autocall/varexist.sas";