Checking if a field exists in a datastep

[chris_e]

I have a file that can have one of two fields site of cov in a table called A1_loc. If the field is Site I want fields 1 and 2 to have "SA" in them and if it is cov I want those fields to have "C" in them. I have tried writing the code in a number of different ways using varnum and varexist, but neither seems to be working. Below is an example of my code with varnum:

%macro test();

	data test1;
		set A1_loc;
		%if varnum('A1_Loc', 'site') > 0 %then %do;
			field1 = "SA";
			field2 = "SA";
		%end
		%else %if varnum('A1_Loc', 'cov') > 0 %then %do;
			field1 = "C";
			field2 = "C";
		%end;
	run;

%mend;

%test();

Thanks

[Reeza · Accepted solution]

VARNUM() function is used differently than how you're trying to use it. 

An example is here:

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0s4xeo2nzqhdtn177qgad6iz69f.htm&docset...

You'd have to open the dataset using OPEN() and then use the functions to check. 

Some other options are using the SASHELP.VCOLUMN table which has the variable list to create your logic and another is using CALL VNEXT which loops through all variables in the data set. 

Here's an example of how that may work. Note that character comparisons are case sensitive so I used the upcase function here. 

*both variables exist;

data class1;
    set sashelp.class;
    site='1';
    A1_loc='2';
    drop name;
run;

*one variable exists;

data class2;
    set sashelp.class;
    site='1';
    drop name;
run;

*neither variable exists;

data class3;
    set sashelp.class;
    drop name;
run;

%macro check_var_exist(dsn=);
    data test;
        set &dsn end=eof;
        length name $32 type $3;
        name=' ';
        length=666;
        var1=0;
        var2=0;

        do i=1 to 99 until(name=' ');
            call vnext(name, type, length);

            if upcase(name)='A1_LOC' then
                var1=1;

            if upcase(name)='SITE' then
                var2=1;
        end;

        if eof then
            do;

                if var1 and var2 then
                    put "&dsn: Both variables found";
                else
                    put "&dsn: Not found";
            end;
    run;

%mend;

%check_var_exist(dsn=class1);
%check_var_exist(dsn=class2);
%check_var_exist(dsn=class3);

---

@chris_e wrote:

I have a file that can have one of two fields site of cov in a table called A1_loc. If the field is Site I want fields 1 and 2 to have "SA" in them and if it is cov I want those fields to have "C" in them. I have tried writing the code in a number of different ways using varnum and varexist, but neither seems to be working. Below is an example of my code with varnum:

%macro test();

	data test1;
		set A1_loc;
		%if varnum('A1_Loc', 'site') > 0 %then %do;
			field1 = "SA";
			field2 = "SA";
		%end
		%else %if varnum('A1_Loc', 'cov') > 0 %then %do;
			field1 = "C";
			field2 = "C";
		%end;
	run;

%mend;

%test();

Thanks

---

[chris_e]

Thanks Reeza, with a few minor modifications to your code I was able to get mine working.

[Astounding]

If I'm reading the problem correctly:

proc contents data=A1_loc noprint out=_contents_ (keep=name);

run;

%let field=COV;

data _null_;

   set _contents_;

   where upcase(name)='SITE';

   call symput(field, 'SITE');

run;

At this point, the macro variable &FIELD contains either COV or SITE, depending on whether SITE was found in the data set.  To continue:

data want;

set A1_loc;

if "&field"='SITE' then do;

   field1 = 'SA';

   field2 = 'SA';

end;

else do;

   field1 = 'C';

   field2 = 'C';

end;

run;

You certainly can use dictionary.columns instead of PROC CONTENTS if that is your preference.

[PGStats]

This might do:

data test;
retain cov ._; /* Some impossible value */
set a1_loc;
if cov = ._ then field1 = "SA";
else field1 = "C";
field2 = field1;
run;

[ChrisNZ]

data TEST1;
  if _N_=1 then do;
    DSID=open('A1_LOC');
    DO_SITE+varnum(DSID,'SITE');
    RC=close(DSID);
  end;
  set A1_LOC;
  if DO_SITE then do;
    FIELD1 = "SA";
    FIELD2 = "SA";
  end;
  else do;
    FIELD1 = "C";
    FIELD2 = "C";
  end;
run;

Since you obviously have a limited understanding of the macro language (you are using it inappropriately here by wrongly running macro statements in that data step ), try to use it as little as possible.

This is a rule even seasoned macro language programmers follow.

The macro language is not needed here. Do not use it.

[RW9]

Keep it simple:

data test1;
  length site cov $50;
  set a1_loc;
  if site ne '' then do;
    field1='SA';
    field2='SA';
  end;
  else do;
    field1='C';
    field2='C';
  end;
run

The length will create the variables, and then any data from the individual contibutors will feed into it, being missing otherwise.

Why do you need two variables exactly the same (field1/2)?