check if a field exist and if not to create a field with missing value

[Ronein]

Hello

I have multiple   data sets that I need to work with.

The problem is that in some  data sets  some fields are not existing.

I will  illustrate the issue with a simple problem.

What is the way to check if a field exist and if not to create a field with missing value

data tbl1;
input ID x1 x2 ;
cards;
1 10 15
2 20 25
3 30 35
4 40 45
5 50 55
;
run;
data tbl1b;
input ID x1 x2 ;
cards;
1 10 15
2 20 25
3 30 35
4 40 45
5 50 55
;
run;


Data tbl2;
set tbl1;
IF x3 not exist then x3=.;
Run;

Data tbl2b;
set tbl1b;
IF x3 not exist then x3=.;
Run;

[andreas_lds]

Idea: create an empty dataset with the expected structure and use if 0 then set in a data step:

data ExpectedStructure;
   length Id x1-x3 8;
   call missing(of _all_); /* prevents not-initialized notes/warnings/errors */
   stop;
run;


data tbl2;
   if 0 then set ExpectedStructure;
   set tbl1;
run;

data tbl2b;
   if 0 then set ExpectedStructure;
   set tbl1b;
run;

[Kurt_Bremser]

Very simple approach: query dictionary.columns:

data tbl1;
input ID x1 x2 ;
cards;
1 10 15
2 20 25
3 30 35
4 40 45
5 50 55
;
run;

proc sql noprint;
select count(*) into :varexist
from dictionary.columns
where libname = "WORK" and memname = "TBL1" and upcase(name) = "X3";
quit;

data tbl2;
set tbl1;
if not &varexist then x3 = .;
run;

[Mar_Lds]

Thank you, worked great!

[Ksharp]

You could create a null sample dataset , and append it into original tables by PROC APPEND or SQL.

data tbl1;
input ID x1 x2 ;
cards;
1 10 15
2 20 25
3 30 35
4 40 45
5 50 55
;
run;
data tbl1b;
input ID x1 x2 ;
cards;
1 10 15
2 20 25
3 30 35
4 40 45
5 50 55
;
run;


proc sql nowarn;
create table sample (id num,x1 num,x2 num,x3 num) ;

create table new1 as
select * from sample 
outer union corr
select * from tbl1;

create table new2 as
select * from sample 
outer union corr
select * from tbl1b;

quit;

[Kurt_Bremser]

Or use a function-style macro to create a boolean value:

%macro check_column(lib=,ds=,var=);
%let did=%sysfunc(open(&lib..&ds));
%let pos=%sysfunc(varnum(&did,&var));
&pos
%let did=%sysfunc(close(&did));
%mend;

data tbl2;
set tbl1;
if ^%check_column(lib=work,ds=tbl1,var=x3) then x3 = .;
run;

Edit: added close in macro 

[Astounding]

Perhaps a simple solution would do?

data want;
   set have;
   x3 = x3;
run;

If X3 already exists, the program leaves it as is.

But if X3 doesn't already exist, the program creates it as numeric, with a missing value.

[gamotte]

Hello,

As long as you use the variable somewhere in the data step, it will exist in the

resulting dataset.

data have;
set sashelp.class;
if missing(x3) then put _N_ "missing value found for X3";
run;

If x3 does not exist, it will be given a default numeric best. format (a note about x3 being uninitialized will also be issued).

If you want to specific column attributes you can set them with length, format, informat, attrib instructions which will also

have the effect of creating the column in case it does not exist.

[Tom]

It is trivial for numeric variables since you can add a LENGTH statement.  If the variable exists it doesn't change it, other than perhaps changing how many of the 8 byte floating point value is actually stored into the output dataset(s).

data tbl2;
  set tbl1;
  length x3 8 ;
run;

If X3 is in TBL1 then its value is unchanged. If the variable X3 was not in TBL1 then it is created and you will get a nice little note in the log from SAS saying that it has not been initialized.