Convert multiline text prompt to table with a single column?

[Jase]

Hi,

Using SAS EG 8.3 and SAS9.4, and SAS is not my usual programming language.

I have a multiline text prompt which I need to convert to a table with a single column (from which I can do a join on another table to filter).

So, essentially, for this:

%LET input_values = 1000456001
2000789002
3008246003;

I need to turn it into:

result

1000456001

2000789002

3008246003

I have tried this code:

%LET input_values = 1000456001
2000789002
3008246003;

data input (keep = result);
string = "&input_values.";
do i=1 by 1 while (scan(string,i,'#') ^= ' ');
result=scan(string,i,'#');
output;
end;
run;

However, there are scenarios where the list of values might contain 10k distinct values and I receive this error code:

ERROR: The text expression length (65537) exceeds maximum length (65534). The text expression has been truncated to 65534 characters.

I presume this is happening because SAS is storing the input_values variable as a single value field rather than an array of values?

And/or the scan function is a SAS macro vairable with the 65534 length limitation?

Can anyone suggest an alternate approach to creating the table of values whilst retaining the use of the prompt?

Or am I stuck having to use something like: 

data my_data;
input result;
cards;
10000001
10000002
10000003
;
run;

(As previously stated, I really prefer to retain the prompt rather than forcing users to copy/paste into the program code.)

[Kurt_Bremser · Accepted solution]

User prompts store the values in macro variables, and macro variables are limited to 64k characters (actually, 65534 as you see in the ERROR message). So you are overstraining what SAS prompts can do for you.

I would look into a method using a text file; your users already have this mass of data in some form, so why not let them save it to a place where the SAS code can pick it up?

[Jase]

Thanks for confirming.