12-08-2015 04:58 PM
Hi all. I been trying to call a character macro variable in an integrity constraint in Dataset procedure inside a macro. It is resolving to the correct string, but the results say " Add/Update failed for data set MCB.TABLE_PRODUCTS because data value(s) do
not comply with integrity constraint aa_mes". However, all my observations have the character value of 201507 for the variable aa_mess. The following is a sample of my code:
%macro table_prod(new1,set1,month1); proc datasets library=&lib nolist; modify &new1; ic create fecha_proc = not null(fecha_proc); ic create aa_mes = check(where=(aa_mes = "&month1")); ic create id_clte = not null(id_clte); ic create Id_prod = not null(id_prod); run; append base=&lib..&new1 data=&lib..correct&set1; run; quit; %mend table_prod; %table_prod(table_products,productos_201507,201507);
I may nto be writing the write code for call a character macro variable or maybe SAS is confused whether it is numeric or a character since it contains numbers. I'll be very appreciative if anyone sees the error in the code that could help me fix my issue.
12-08-2015 05:25 PM
12-08-2015 05:38 PM - edited 12-08-2015 05:38 PM
A trailing space wouldn't matter. I rather suspect a leading blank.
What does the output of the following step look like?
proc sql; select distinct put(aa_mes, $hex14.) from &lib..correctproductos_201507 where aa_mes ne '201507'; quit;
12-09-2015 04:48 AM
Not sure if I see the benefit of using a check constraint of something that easily can solve with a normal where-clause.
Also, usually not too fond of physically separate data by time period. Makes query much harder (unless you will use them as members in a SPD Server cluster).