topic Re: check if a field exist and if not to create a field with missing value in SAS Programming

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

Ronein — Thu, 19 Dec 2019 12:14:46 GMT

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;

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

andreas_lds — Thu, 19 Dec 2019 12:29:02 GMT

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;

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

Kurt_Bremser — Thu, 19 Dec 2019 12:29:02 GMT

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;

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

Ksharp — Thu, 19 Dec 2019 12:35:51 GMT

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;

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

Kurt_Bremser — Thu, 19 Dec 2019 12:44:26 GMT

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

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

Astounding — Thu, 19 Dec 2019 13:44:51 GMT

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.

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

gamotte — Thu, 19 Dec 2019 13:58:59 GMT

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.

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

Tom — Thu, 19 Dec 2019 14:55:14 GMT

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.

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

Mar_Lds — Sat, 04 Jul 2020 07:19:26 GMT

Thank you, worked great!