Use Macro list in DI Studio extract transformation

[MattiL]

Hi,

Would someone be able to help me with the following issue I have. 

I use proc sql into method in pre code to make &id_list separated by , .

This gives me the following list for example 1234, 1345, 1456, 1567

I have a numeric id column where i would like to use the list something like this

where put(id,8.) in("&id_list."').

I get no results. I believe that the list elements should be like this '1234', '1345', '1456', '1567'

OR i should be able to change list to numeric but not sure if possible.

So the question is how to manipulate the list element to have ' ' ?

Br

Matti

[Amir · Accepted solution]

Hi,

You do have an extra single quote just before the closing bracket:

where put(id,8.) in("&id_list."');

You might not need to perform any conversions or use any quotes. Have you tried something like:

where id in (&id_list.);

If you really want quotes around the values in your list then when you use SQL to create the list then you could try using the quote() function, e.g.:

select quote(cats(id)) into :id_list separated by ','

As quote() requires a string I also used the cats() function to convert id to a string and then strip the result of leading and trailing spaces.

Try displaying the result of your put() conversion and you might find you have leading spaces which could reduce the chances of finding a match in the list. Also display the value of &id_list to make sure it looks like what you expect.

Kind regards,

Amir.

Edited for typo and formatting.