topic Re: Splitting a text using substr in SAS Procedures

Splitting a text using substr

namrata — Sat, 07 Dec 2013 00:04:15 GMT

I have a column of codes containing alphanumeric characters:

Code

1A2B3C4D

2B3C3D

1A1G2C2B3A

and so on....

I need to split the code into 2-character variables thus:

Code Type_a Type_b Type_c Type_d

1A2B3C4D 1A 2B 3C 4D

2B3C3D 2B 3C 3D

This is the code I am using:

data f12;

set f12;

type_a=substr(code,1,2);

type_b=substr(code,3,2);

type_c=substr(code,5,2);

type_d=substr(code,7,2);

type_e=substr(code,9,2);

run;

The code runs but returns this error: NOTE: Invalid second argument to function SUBSTR at line 2007 column 8.

Each time, I run the code, the error pertains to a different line/row.

I thought I could use substrn to avoid the problem but I was testing the difference in output with both substr and substrn but the results come off the same.

Should I use the Strip or Trim function as in: type_a=substr(trim(code),1,2)?

Thanks!

Re: Splitting a text using substr

Reeza — Sat, 07 Dec 2013 00:40:17 GMT

On your second observation, when you run type_d, the values to substr are invalid because they're out of bounds, the string is too short.

I suggest a loop instead.

data have;

length code $10.;

input code;

cards;

1A2B3C4D

2B3C3D

1A1G2C2B3A

;

run;

data want;

set have;

array type(5) $ type_a type_b type_c type_d type_e;

i=1; j=1;

do while(j-1<length(code));

type(i)=substr(code, j, 2);

i+1;

j+2;

end;

run;

Re: Splitting a text using substr

Tom — Sat, 07 Dec 2013 01:22:31 GMT

Your problem is that the variable CODE must have been defined as length $9. So you cannot use SUBSTR(CODE,9,2) because 2 is an impossible length when starting on the last character.

One way to avoid having to specify the length is to let SAS do the truncation by defining the target variables as length $2.

data f12;

set f12;

array type $2 type1-type5;

do i=1 to 5 while (2*i-1 <= length(code));

type(i) = substr(code,2*i-1) ;

end;

run;

Re: Splitting a text using substr

namrata — Sat, 07 Dec 2013 02:28:31 GMT

Thanks a lot,Reeze and Tom.

I have a quick question: I understand that the string in my variable' is too short for type_d for some of the observations.

However, why does the code run in some cases even when the string is too short- the error is returned for different rows every time I run the code?

Another question is I am trying to import a csv dataset that contains variables for address and dates too. I use Proc Import and the log shows the number of observations,rows/variables created in SAS. The dataset is therefore, created.The log however, also gives a message that the import is unsuccessful and when I scroll up, I find errors regarding invalid data for date. Is the message 'import unsuccessful' just because of those 'invalid data' errors? In that case, I can actually ignore the message since I shall anyway drop the variables for address and date.

Thanks a lot!

Re: Splitting a text using substr

Reeza — Sat, 07 Dec 2013 02:32:18 GMT

The row reference that the error return are from the line number of the code submitted, not with your observations. Since each code is submitted with new line numbers the location of the error changes.

Please post your new question as a different post.

Re: Splitting a text using substr

Tom — Sat, 07 Dec 2013 02:41:18 GMT

If you use PROC IMPORT to convert a CSV file to a SAS dataset it will have to GUESS what type of variable to define for each column. CSV files have no place store variable definitions. So some CSV files the maximum length of the value in the "CODE" column might be 8 and in others it might be 10. So sometimes PROC IMPORT will define it with length $8 and other times it will be defined as length $10. (Not to mention when it only sees digits in the field so it defines CODE as number instead of character)

If your files have a regular structure then just write a data step to read the CSV file and then you have complete control over how the variables are defined.

Re: Splitting a text using substr

data_null__ — Sat, 07 Dec 2013 14:26:00 GMT

SUBPAD

18         data _null_;
19            a='abc'; 
20            x = subpad(a,3,2);
21            put (_all_)(=);
22            run;

a=abc x=c

Re: Splitting a text using substr

namrata — Mon, 09 Dec 2013 18:34:35 GMT

Thanks, everyone! I really appreciate the feedback.

@Tom: You had explained the datastep/proc import in another post(below)-specify the length of the variables. Is that what you are suggesting that I should do?

https://communities.sas.com/thread/40451

In this case, I shall anyway drop the variables(with respect to which the errors are mentioned in SAS log). Should I still be concerned if there is a message 'Import unsuccessful'?

Thanks!

Re: Splitting a text using substr

Tom — Mon, 09 Dec 2013 18:55:29 GMT

In general if you want to do a quick ad hoc usage of an unknown CSV file then PROC IMPORT is pretty good, but know its limitations. However if you are going to be doing this frequently and the format of the CSV is supposed to be standardized then you should read it in with a data step. Especially if you will be combine the data with other data where the type and length of the variables could caused trouble.

For your original issue changing the program to use SUBPAD() function instead of SUBSTR() function should eliminate the errors about invalid argurments for SUBSTR().

Re: Splitting a text using substr

namrata — Mon, 09 Dec 2013 22:17:49 GMT

Well,SUBPAD() works!Thank you,data_null_.

Had a quick question while I change my codes. What is the purpose of the statement 'put (_all_)(=);' above in the SUBPAD() command?

Namrata

Re: Splitting a text using substr

art297 — Mon, 09 Dec 2013 22:34:47 GMT

: I think data_null_ only used that to show you what the results looked like.

data_null_: Thanks! I can't recall ever even seeing that function (i.e., subpad)

Re: Splitting a text using substr

namrata — Mon, 09 Dec 2013 22:44:18 GMT

Thanks Arthur! I never used the put statement and so was unsure

Re: Splitting a text using substr

SteveNZ — Tue, 10 Dec 2013 00:00:15 GMT

Could also do it this way:

data want (keep = codes type_a type_b type_c type_d type_e) ;

length type_a type_b type_c type_d type_e $2 ;

set have ;

ExpressionID = prxparse('/\d\w/');

start = 1;

stop = length(codes);

array tcodes{5} $ type_a type_b type_c type_d type_e ;

call prxnext(ExpressionID, start, stop, codes, position, length);

do i = 1 to 5 ;

if position > 0 then do ;

tcodes{i} = substr(codes, position, length);

call prxnext(ExpressionID, start, stop, codes, position, length);

end ;

end;

run;

Re: Splitting a text using substr

kuridisanjeev — Tue, 10 Dec 2013 05:45:15 GMT

Well,

Why don't we include Macros in this topic.

Here is the way by using Macros,

Data A;

input Code $;

cards;

1A2B3C4D

2A3B4C

1A2B

;

run;

%MACRO t;

Data Aa;

Set A;

%do i = 1 %to 8 %by 2;

Var&i=Subpad(Code,&i,2);

%end;

run;

%mend;

%t;

Regards,

Sanjeev.K

Message was edited by: sajeev kuridi Substr function replae with Subpad.

Re: Splitting a text using substr

Patrick — Tue, 10 Dec 2013 09:49:29 GMT

For me macro code is harder to read than normal base SAS code so my personal approach is to only use macro coding if there is a real gain in doing so. Also: You're still using "substr()" instead of "subpad()" so the initial issue would remain.

Re: Splitting a text using substr

kuridisanjeev — Tue, 10 Dec 2013 10:04:37 GMT

Hi Patrick ,

I am not saying that my approach is the best one, but this is the one for the people who don't like complex do loops or Perl regular expressions.

And while writing the code,i thought of use Subpad function,but my fingers automatically typed Substr function as i am frequent user of that function ,infact i haven't recognised that typo..Thanks for correcting me.

Regards,

Sanjeev.K

Re: Splitting a text using substr

namrata — Tue, 10 Dec 2013 21:34:37 GMT

Tom

You are right. Since I was using Proc Import for a couple of datasets and now, am trying to vertically combine some of these processed datafiles, the error pops up: a couple of variables are numeric in some places while in others it is character.

Well, I see that I shall need to use a data step to import my data files

Thanks everyone!!

You all are a great help

Namrata