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;
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;
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;
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;
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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.