topic Calling a character macro variable in a integrity constraint in SAS Programming

Calling a character macro variable in a integrity constraint

mc — Tue, 08 Dec 2015 21:58:10 GMT

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.

Re: Calling a character macro variable in a integrity constraint

Reeza — Tue, 08 Dec 2015 22:01:25 GMT

What does a proc contents show for that variable, aa_mes?

Re: Calling a character macro variable in a integrity constraint

mc — Tue, 08 Dec 2015 22:12:38 GMT

The variable aa_mes only has description of TYPE and LEN, which are Char and 7 respectively.

Re: Calling a character macro variable in a integrity constraint

Reeza — Tue, 08 Dec 2015 22:25:13 GMT

7? You're providing a 6 char, try adding a trailing space to your comparison? You can also try your where comparison in a data step and see if it works.

Re: Calling a character macro variable in a integrity constraint

FreelanceReinh — Tue, 08 Dec 2015 22:38:53 GMT

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;

Re: Calling a character macro variable in a integrity constraint

LinusH — Wed, 09 Dec 2015 09:48:45 GMT

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