As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.
Here are some tips for How to share your SAS knowledge with your professional network.
Solutions in the Round -- Check if a Variable Exists in a Dataset
From the WUSS abstract for this topic
It is often necessary to check if a variable exists in a dataset. We will identify several methods for doing this and will discuss the merits of each. Join us for this group discussion! Solutions in the Round is an exciting new section [at WUSS 2014]. The format is simple: we sit in a circle and discuss the many ways to approach and address a programming problem, and we'll identify situations where one approach might be more useful than another. We will keep notes, and those notes will be posted on sasCommunity.org where we can continue the discussion and allow other to join in after the conference. We hope to engage users from all different perspectives and experience levels to participate in these discussions.
Discussion at WUSS
- PROC CONTENTS
- look at output window
- create an output dataset
PROC CONTENTS data=x out=y noprint; RUN;
- Usually more efficient to use DICTIONARY.COLUMNS as the SASHELP views call the DICTIONARY tables
- SQL Dictionary tables (see discussion below)
- CALL VNAME (can someone elaborate?)
- PROC METADATA (have to have metadata) can someone provide an example?
- Extended attributes in 9.4+ can someone provide an example?
- SYSFUNC VARNUM (see discussion below)
- CALL ATTRB can someone provide an example?
- While there is an ATTRIB statement, I am unfamiliar with an ATTRIB function and believe that this was a mistaken suggestion. --Art Carpenter (talk) 17:45, 11 January 2015 (CST)
Further Discussion -- Open to All
Please join the conversation! Also, if you were one of the live participants, please feel free to correct any mistakes or omissions from our original discussion.
- It would be great to add examples for each of the suggestions, above, as well as further discussion of when they might and might not be useful. --Otterm1 (talk) 13:35, 2 January 2015 (CST)--Otterm1 (talk) 13:35, 2 January 2015 (CST)
- Macro-based approach via SYSFUNC VARNUM:
%* Open the dataset *; %let dsid = %sysfunc(open(work.dataset)); %if %sysfunc(varnum(&dsid, x)) > 0 %then %put NOTE: Variable x exists!; %* Close the dataset (important!); %let rc = %sysfunc(close(&dsid));
This is particularly useful inside of a macro that accepts a variable or list of variables in a dataset as a parameter.
- Dictionary tables in PROC SQL:
proc sql; select name from dictionary.columns where libname = 'WORK' and memtype = 'DATA' and memname = 'SOMEDATASET' and upcase(name) = 'X'; quit;
If the result set from the query is empty then the variable does not exist in the dataset. The variables of interest in the COLUMNS dictionary table are:
- LIBNAME, the libref name in all caps
- MEMTYPE, the the member type, i.e. data or view, of the object in all caps
- MEMNAME, the name of the dataset
- NAME, the name of the variable
data _null_; set sashelp.vcolumn; where libname = 'WORK' and memtype = 'DATA' and memname = 'SOMEDATASET'; if upcase(name) = 'X' then put 'NOTE: Variable x exists!'; run;
The VCOLUMN view in the SASHELP library is akin to the COLUMNS SQL dictionary table, however the SQL dictionary table will generally be faster as the SASHELP views are generated using the SQL dictionary tables.
- CALL VNAME:
I'm not sure how call vname would be helpful in this context, but the VNAMEX function definitely would be. Not only will it check if a variable exists, but isn't case sensitive. e.g.:
data test; if 0 then set sashelp.class; if missing(vnamex('name')) then x1=1; if missing(vnamex('gender')) then x2=1; if missing(vnamex('sex')) then x3=1; if missing(vnamex('SEX')) then x3a=1; if missing(vnamex('age')) then x4=1; if missing(vnamex('Height')) then x5=1; if missing(vnamex('Weight')) then x6=1; if missing(vnamex('score')) then x7=1; put (x:)(=); stop; run;
--Art T 09:30, 11 January 2015 (CST)
A disadvantage of the use of VNAMEX function is that the use of a variable name that is not on the data set will result in an error. In this example the variables GENDER and SCORE are not on the data set SASHELP.CLASS. --Art Carpenter (talk) 17:34, 11 January 2015 (CST) VNAMEX will
- USING A DROP STATEMENT IN A DATA STEP:
One method that wasn't suggested is using a drop statement in a data step. It isn't case sensitive and will produce warnings in the log for any variable that isn't in the dataset. In the following example, gender and score are not on the data set SASHELP.CLASS:
data test (drop=SEX gender Age height weight score); if 0 then set sashelp.class; stop; run;
--Art T 08:28, 12 January 2015 (CST)