Stop proceeding macro if column already exists

Reply
Occasional Contributor
Posts: 15

Stop proceeding macro if column already exists

[ Edited ]

Hi,

 

I need to create macro which will alter table and add new column. But if the column already exists the macro should stop processing.

 

Every month I will add column with customer status for a given month. For example Cust_status_116, cust_status_216 etc. Next column should be cust_status_316. But I need to check if this column already exists in my table and if yes whole macro should be stopped. 

 

Could please anybody help me with this?

 

Thanks

Super User
Posts: 5,387

Re: Stop proceeding macro if column already exists

You could use SASHELP.VCOLUMN or DICTIONARY.COLUMNS (have not SAS at my fingertips, so names might not be 100% accurate).

There are also SAS file functions to help you navigate the metadata of an existing data set.

And the output from PROC CONTENTS/DATASETS....

 

But, creating loads of columns will lead to syntax heavy query, and seldom efficient. You should consider a data model where you append/insert rows in case of new data. Will probably be easier to maintain/query.

Data never sleeps
Frequent Contributor
Posts: 129

Re: Stop proceeding macro if column already exists

I agree with what Linus said.

Nevertheless here is how to do what you want:

%macro alterTable;

   PROC SQL noprint;
      SELECT *
      FROM sashelp.vcolumn
      WHERE upcase(libname) eq upcase("YourLib")
        AND upcase(memname) eq upcase("YourDataset")
        AND upcase(name) eq upcase("YourVariableName") 
      ;
   QUIT;
   %put &=sqlobs.;
   %if &sqlObs. ne 0 %then %goto exit;

   DATA _NULL_;
      put 'W' 'ARNING: performing regular code';
   RUN;

%exit: %mend alterTable;
%alterTable;
________________________

- Cheers -

Occasional Contributor
Posts: 15

Re: Stop proceeding macro if column already exists

[ Edited ]

Thanks. It works but only when the &sqlObs. is not 0. Whet it is 0 (it means that my table does not contain a given column name) this error has been occurred: 

ERROR: Incorrect length in SAS Metadata Repository for column OFFER_ID.

 

Do you know where could be a problem? Column OFFER_ID is not used in my code...

Thank you very much.

SAS Super FREQ
Posts: 706

Re: Stop proceeding macro if column already exists

hi

 

also have a look here http://www.sascommunity.org/wiki/Tips:Check_if_a_variable_exists_in_a_dataset

shows a macro that will return either 1 or 0 depending on whether a variable exists.

 

Bruno

Ask a Question
Discussion stats
  • 4 replies
  • 345 views
  • 0 likes
  • 4 in conversation