topic Re: check if column exists & add if not in SAS Programming

check if column exists & add if not

me55 — Tue, 18 Apr 2017 16:19:14 GMT

okay, i have kind of an odd situation where the table i am importing into sas might or might not have a needed column of data. if it does not then i need to add that column to the table. i was just going to add the column using sql but i need help figuring out how write that check to see if the column exists first. can anyone help me with that. i think i am getting close just cant nail it down...

so basically something like...

if table.column does not exist,

proc sql;

alter table add column char(20);

(or else check another column...)

end;

Re: check if column exists & add if not

novinosrin — Tue, 18 Apr 2017 16:22:37 GMT

https://communities.sas.com/t5/Base-SAS-Programming/SAS-query/m-p/350433#M81457

check the above link, you should have your solution

Re: check if column exists & add if not

ballardw — Tue, 18 Apr 2017 16:38:13 GMT

The link @novinosrin shared has several solutions. The last one, not the accepted solution, may be the easiest to implement and maintain long term.

Re: check if column exists & add if not

Astounding — Tue, 18 Apr 2017 16:41:11 GMT

Given that you are looking to add a character variable, and you know the length should be 20, you would need a slight modification:

data want;

length column_name $ 20;

set have;

run;

Re: check if column exists & add if not

me55 — Tue, 18 Apr 2017 17:03:11 GMT

oh sorry, i just put that in there. the column i am looking to add is an id column that will be autonumbered via _n_...

Re: check if column exists & add if not

Tom — Tue, 18 Apr 2017 17:15:09 GMT

MUCH easier if you do NOT use PROC SQL.

So if you requre that the variable MUST_EXIST is on the data and it should be a number then you could just run this step after "importing" the data.

data want ;
   length must_exist 8 ;
   set have ;
run;

Only trouble you will get is if the variable DID exist, but was of the wrong type. Or it is was character and had values longer than what you specified in your code which would cause truncation of the long values.

If you want to add a record counter and when the variable does exist (or it exists with missing values) set it to the observations number then you might be able to use the automatic step counter varaible _N_.

data want ;
   length recno 8 ;
   set have ;
   recno = coalesce(recno,_n_);
run;

Re: check if column exists & add if not

me55 — Tue, 18 Apr 2017 17:44:35 GMT

the thing is the field may exist or not. basically i need something that will check if the field exists alread in the table, if it does not, add it and if it does then do nothing. after i import the table, it might be there and it might not. i need it to check that and i need it to run within the context of a larger program and not error out and stop the rest of the program from running AND i need it set to the table because this field is going to be important later on in the process.

when i tried, within the context of that macro (the link above...)

data want;

set TABLE;

ID=_n_;

run;

it added the column but not permanently to the table so in later on in the program when this field is important. this field is a marking field for data identification later on.

Re: check if column exists & add if not

Astounding — Tue, 18 Apr 2017 17:58:20 GMT

OK, for that:

data want;

id = _n_;

set have;

run;

If HAVE contains ID, its values will overwrite the values copied from _N_.

Re: check if column exists & add if not

me55 — Wed, 19 Apr 2017 14:28:50 GMT

okay, i got the answer. i combined novinosrin & tom & astoundings answers. when i originally was using the link from novinosrin, when i made a change it would not hold the change. i think what it was doing was opening the table early in the code, holding it open through the checks and where the changes were made and that was making a change to the open version of the file but not the permanent file so i moved the close line up and put the result into a variable and things work great now.

thanks for the help, here is the working code i am using...

%macro VarExist(ds, var);
%local rc dsid result resx;
%let dsid = %sysfunc(open(&ds));
%let resx = %sysfunc(varnum(&dsid, &var));
%let rc = %sysfunc(close(&dsid));

%if &resx > 0 %then %do;
%let result = 1;
%put NOTE: Var &var exists in &ds;
%end;

%else %do;
%let result = 0;
%put NOTE: Var &var not exists in &ds;

data &dsid;
&var=_n_;
set &dsid;
run;

%end;
%mend VarExist;

%VarExist(TABLE, FIELD);