topic Re: Field Separation into multiple fields in SAS Programming

Field Separation into multiple fields

anweinbe — Fri, 10 Jan 2020 00:22:23 GMT

Hello all,

I have a file that has a column called "issue" that includes several numerical codes for issues identified from testing. For example, the entry in the field might be "|38|15|" indicating that issues 38 and 15 were identified. How do I separate this field out into multiple columns so "38" is in one column and "15" is in another? I would also like to remove the "|"s.

Some fields have no issue codes, some can have up to 5!

Thank you for your insights!

Re: Field Separation into multiple fields

novinosrin — Fri, 10 Jan 2020 00:36:17 GMT

Hi @anweinbe Do you mean this?

data have;
num_code="|38|15|" ;
run;

data want;
set have;
array want(5)$;
do _n_=1 to countw(num_code,'|');
 want(_n_)=scan(num_code,_n_,'|');
end;
run;

If not, please provide data samples of HAVE and WANT. Thank you!

Re: Field Separation into multiple fields

anweinbe — Fri, 10 Jan 2020 00:58:05 GMT

This appeared to work...

I do have a few more question if you don't mind.

1. Is there an easy way to create a field that tells me how many error codes I have? So in the example I gave the field would report "2". If there code was |15|23|23| it would report "3" etc.

2. What syntax would I need for an If statement to search for a particular code? I was thinking something like this...however what do I put in place of the word "contains"

If field contains |15| then Issue_1 = 0;

Re: Field Separation into multiple fields

novinosrin — Fri, 10 Jan 2020 01:08:41 GMT

HI @anweinbe The modified code below should suffice for some with issue or none, and the num of error

data have;
num_code="|38|15|" ;
output;
call missing(num_code);
output;
run;

data want;
set have;
array want(5)$;
num_of_error=countw(num_code,'|','t');
if num_of_error then
do _n_=1 to num_of_error;
 want(_n_)=scan(num_code,_n_,'|');
end;
run;

for 2. You need to explain/show how your output should look like. Your words and sentences is making me assume things. plz

Re: Field Separation into multiple fields

anweinbe — Fri, 10 Jan 2020 01:18:21 GMT

Let me clarify...

For the first question, let's assume that the record shows |38|15|. I would like a new field to be created called "Num_Issues" that should show a value of 2. Let's assume that the next record shows |15|3|5|8|. I would like the field "Num_Issues" to show a value of 4.

For the second question, lets assume the current field shows |38|15|. I want to create a field called "Issue_38" that is a "1" if 38 is found within the string, and "0" if 38 is not found within the string.

Re: Field Separation into multiple fields

anweinbe — Fri, 10 Jan 2020 01:22:31 GMT

I apologize, the code you send for the first question did work! I forgot to rename one of the fields...

Just the 2nd question is left!

Re: Field Separation into multiple fields

anweinbe — Fri, 10 Jan 2020 01:51:14 GMT

I figured out the last part. I needed to use "findw()"