09-06-2016 04:29 AM - edited 09-06-2016 05:05 AM
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?
09-06-2016 05:16 AM
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.
09-06-2016 05:36 AM
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 -
09-09-2016 09:15 AM - edited 09-09-2016 09:16 AM
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.
09-06-2016 05:55 AM
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.