DATA Step, Macro, Functions and more

Calling a character macro variable in a integrity constraint

Reply
New Contributor mc
New Contributor
Posts: 4

Calling a character macro variable in a integrity constraint

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.

Super User
Posts: 19,770

Re: Calling a character macro variable in a integrity constraint

What does a proc contents show for that variable, aa_mes?
New Contributor mc
New Contributor
Posts: 4

Re: Calling a character macro variable in a integrity constraint

The variable aa_mes only has description of TYPE and LEN, which are Char and 7 respectively.
Super User
Posts: 19,770

Re: Calling a character macro variable in a integrity constraint

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.
Trusted Advisor
Posts: 1,117

Re: Calling a character macro variable in a integrity constraint

[ Edited ]

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;
Super User
Posts: 5,424

Re: Calling a character macro variable in a integrity constraint

Posted in reply to FreelanceReinhard

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

Data never sleeps
Ask a Question
Discussion stats
  • 5 replies
  • 268 views
  • 0 likes
  • 4 in conversation