BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
me55
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
me55
Quartz | Level 8

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);

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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

 

check the above link, you should have your solution

me55
Quartz | Level 8

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. 

ballardw
Super User

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

Astounding
PROC Star

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;

me55
Quartz | Level 8

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_...

Astounding
PROC Star

OK, for that:

 

data want;

id = _n_;

set have;

run;

 

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

Tom
Super User Tom
Super User

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;
me55
Quartz | Level 8

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);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 29173 views
  • 3 likes
  • 5 in conversation