Whether variable exists in a dataset

[gpv2000]

I am trying to find if a variable exists in a dataset and if it does than do some processing on that variable.

data work.ZZ_lesion;
set source.B_lesion;
%let dsid=%sysfunc(open(work.ZZ_lesion));
%if %sysfunc(varnum(&dsid,lesion_DECOD)) > 0 %then %do;
lesion = LESION_DECOD;
%END;
%let rc=%sysfunc(close(&dsid));
run;

But i get this warning, 
WARNING: Argument 1 to function VARNUM referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.

How do I get rid of the warning?

[Reeza]

Because the data set you're trying to access (ZZ_Lesion) is open and being written to which is problematic. Did you mean to check B_Lesion instead? ZZ_Lesion is the output data set.

[gpv2000]

AFter I set my dataset, I want to check if that variable exists, if it does then i want to process it if not, then i don't wish to do anything in that dataset. I thought i would be able to use open and then write to it if my condition works. Do you have a suggestion?

[Reeza]

The variable is coming from the input data set, use the input data set name in the OPEN function. Using OPEN in the data step with the data set you're creating at the same time is asking for trouble.

You could also use CALL VNEXT() to iterate through the list of variables in a current data step and build your logic around that. The example in the documentation illustrates how to get the values you need and loop through.

[gpv2000]

Thank you. I will look into VNEXT.

[ballardw]

---

@gpv2000 wrote:

AFter I set my dataset, I want to check if that variable exists, if it does then i want to process it if not, then i don't wish to do anything in that dataset. I thought i would be able to use open and then write to it if my condition works. Do you have a suggestion?

---

Before going a long ways down a path chasing a single variable; are you going to do this for multiple variables in a data set?

Are the "manipulations" going to be any more complicated than "newvar=oldvar"?

It is very likely that we can build a control data set with the desired elements that can be used to write the needed data step with CALL EXECUTE, but a bit more detail will help.

Existence of variables needs to be done outside of the data step, otherwise almost any code that doesn't create a critical error will likely add a variable of that name to the data set.

One way is below. In this case I am interested if any of the variables name, age, grade or school exist in the SASHELP.CLASS data set. So query the dictionary.columns , which has a list of every single variable in every set available in the session at the time the code runs. The libname and memname, (library and dataset names) are stored in upper case but the actual name of a variable may be in mixed case so I upcase the variable that contains the names of variables, name, and compare that to an upper case version of the text I am searching for. Only the matches in the list will return in the data set.

proc sql;
   create table work.names as
   select name
   from dictionary.columns
   where libname='SASHELP' and memname='CLASS'
      and upcase(name) in ('NAME' 'AGE' 'GRADE' 'SCHOOL')
   ;
quit;

And a brief example of using that output to create a new data set and use the information:

data _null_;
   set work.names end=lastone;
   if _n_= 1 then do;
   call execute ('data work.out;');
   call execute ('set sashelp.class;');
   end;
   if upcase(name)='NAME' then 
      call execute('newname= lowcase(name);');
   if upcase(name)='AGE' then 
      call execute ('Agechar = put(age,z3.);');
   if lastone then call execute('run;');
run;

When you run this code you would see in the log something like:

1   + data work.out;
2   + set sashelp.class;
3   + newname= lowcase(name);
4   + Agechar = put(age,z3.);
5   + run;

Which shows the code submitted.

Or alternatively you could use the data _null_ to write the code to a program file using Put statements and a FILE "path\program.sas"; statement that would have the program text you could run separately.

[gpv2000]

Thank you for your reply. Yes, I will be doing it for multiple variables. I am working on creating a standard program which will be used across different studies. I have a superset of possible variables that can exist, but since every study is different, it may or may not have those variables. But since i am trying to develop a standard program i want to include as many as possible. I believe i have to check for 10 such variables and build simple logic around them if they do exist.

[Reeza]

If you're doing standard programming are you aware of the shortcut lists for variables? Those can often save you a lot of time as you generalize programs by using prefixes or lists of variables.

[ballardw]

---

@gpv2000 wrote:
Thank you for your reply. Yes, I will be doing it for multiple variables. I am working on creating a standard program which will be used across different studies. I have a superset of possible variables that can exist, but since every study is different, it may or may not have those variables. But since i am trying to develop a standard program i want to include as many as possible. I believe i have to check for 10 such variables and build simple logic around them if they do exist.

---

You likely also need to check on the variable type and if character possibly the length of the variable as well.

I would suggest modifying my Proc SQL to include getting the type and label from the dictionary table. If your studies are in different libraries you might want LIBNAME in ('STUDY1' 'STUDY2' 'STUDY3') and possibly exclude the MEMNAME in the where statement to get a list of all of the libraries, members and variables that match in your list first.

Check the types and lengths as if you attempt to assign a value greater than the current length the value will be truncated and you need different code.

[Tom]

---

@gpv2000 wrote:
Thank you for your reply. Yes, I will be doing it for multiple variables. I am working on creating a standard program which will be used across different studies. I have a superset of possible variables that can exist, but since every study is different, it may or may not have those variables. But since i am trying to develop a standard program i want to include as many as possible. I believe i have to check for 10 such variables and build simple logic around them if they do exist.

---

Your code will be easier to build, test and maintain if you separate out the part that is checking the metadata/structure of the tables from the part that is using the tables.  That could be as simple as running one block of code that checks for the 10 variables and creates 10 macro variables that the later code can use to generate conditional code.

%let dsn=study1.dosing ;
proc contents data=&dsn noprint out=contents; run;
proc sql noprint;
select max( upcase(name)='STARTTIME') 
     , max( upcase(name)='STOPTIME')
  into :has_starttime
     , :has_stoptime
  from contents
;
quit;

data dosing ;
  set &dsn ;
%if &has_starttime and &has_stoptime %then %do;
* conditional code ;
...
%end;
run;

[ChrisNZ]

>After I set my dataset, I want to check if that variable exists.

This doesn't make sense. If it exist it is because it's coming from the data set being read isn't it?

In which case this works:

%macro t;
  %local dsid rc;
  data CLASS;
    set SASHELP.CLASS;
    %let dsid=%sysfunc(open(SASHELP.CLASS));
    %if %sysfunc(varnum(&dsid,AGE)) %then %do;
      AGE2 = AGE;
    %end;
    %let rc=%sysfunc(close(&dsid));
  run;
%mend;
%t