topic Re: separating a string variable into different variables in SAS Programming

separating a string variable into different variables

dustychair — Mon, 25 Feb 2019 02:48:19 GMT

Hi all,

Although there are similar questions in the community their answers didn't work for me, maybe I did something wrong. I have a string data includes numbers. I'd like to separate them to 5 digits numbers. For example,

Have Want

O_ID O_ID1 O_D2 O_ID3

123451122312390 12345 11223 12390

1234690876 12346 90876

I'll appreciate for any comment, any help

Re: separating a string variable into different variables

novinosrin — Mon, 25 Feb 2019 03:19:34 GMT

data have;
str='123451122312390';
output;
str='1234690876';
output;
run;

data want;
set have;
array want(9) $5; /*increase your array subscript to whatever value you want*/
call pokelong(str,addrlong(want1),vlength(str));
run;

Re: separating a string variable into different variables

34reqrwe — Mon, 25 Feb 2019 03:19:53 GMT

The basic idea is

data have ; 
length oid $50.;
oid='123451122312390'    ;output;
oid='1234690876'          ;output;
run;

data want1;
	set have;
	 oid1= substr(oid,1,5);
	 oid2=substr(oid,6,5);
run;

Or in a simple macro

%macro split;
 data want2;
 set have;
	%do i = 1 %to 100 %by 5 ; 
	%put &i;
	oid&i = substr(oid, &i , 5);
	%end;
 run;
 %mend split;
 %split;

you could enhance the macro by working out the (maximum length / 5) of your initial variable and therefore how many new variables you would need to create .

Re: separating a string variable into different variables

novinosrin — Mon, 25 Feb 2019 03:28:09 GMT

No need for macro

data have;
str='123451122312390';
output;
str='1234690876';
output;
run;

 
data want;
set have;
array want(9) $5; /*increase your array subscript to whatever value you want*/
i=0;
do p=1 by 5 while(p<length(str));
i+1;
want(i)=substr(str,p,5);
end;
drop p i;
run;

Re: separating a string variable into different variables

novinosrin — Mon, 25 Feb 2019 03:51:11 GMT


data have;
str='123451122312390';
output;
str='1234690876';
output;
run;

data want;
set have;
array want(9) $5; /*increase your array subscript to whatever value you want*/
j=0;
do i=1 to length(str);
k=mod(i,5);
if k=1 then do;
j+1;
want(j)=substr(str,i,5);
end;
end;
drop i j k;
run;

Re: separating a string variable into different variables

Satish_Parida — Mon, 25 Feb 2019 08:44:20 GMT

This code gives the expected result, I have used some test data.

data have;
input o_id;
format o_id best32.;
cards;
123451122312390
12345678909876
123456789012345678901
1111111111
;
run;

proc sql noprint;
select max(length(strip(put(o_id,32. -L)))) into :max_len from have;
quit;

%let var_length=5;
%let no_of_cols=%eval(&max_len. / &var_length. + 1);

data want(drop=i init);
set have;
array o_id_[&no_of_cols.];
init=1;
do i=1 to &no_of_cols.;
	if i ne 1 then init=(i-1)*&var_length.;
	o_id_[i]=substr(strip(put(o_id,32. -L)),init,&var_length.);
end;
run;

Please let us know if it worked for you.

Re: separating a string variable into different variables

Ksharp — Mon, 25 Feb 2019 12:53:16 GMT

data have;
input o_id $22.;
cards;
123451122312390
12345678909876
123456789012345678901
1111111111
;
run;
data temp(index=(o_id));
 set have;
 length _id $ 200;
 _id=o_id;
 do i=1 to length(_id) by 5;
  temp=substr(_id,i,5);
  if not missing(temp) then output;
 end;
drop _id i;
run;
proc transpose data=temp out=want;
by o_id;
var temp;
run;

Re: separating a string variable into different variables

Patrick — Mon, 25 Feb 2019 14:09:08 GMT

or this way

data have;
  input o_id $22.;
  cards;
123451122312390
12345678909876
123456789012345678901
1111111111
;
run;

data _null_;
  if 0 then set have(keep=o_id);
  call symputx('n_vars',ceil(vlength(o_id)/5));
  stop;
run;
%put &=n_vars;

data want(drop=_:);
  set have;
  array o_id_ {&n_vars} $5;
  do _i=1 to &n_vars;
    o_id_[_i]=substrn(o_id,(_i-1)*5+1,5);
    if missing(o_id_[_i]) then leave;
  end;
run;

Re: separating a string variable into different variables

mansour_ib_sas — Mon, 25 Feb 2019 14:48:52 GMT

I propose this solution:

data test;
input O_IDO best32.;
format O_IDO best32.;
cards;
123451122312390
1234690876
123469087
; run;

data test1(drop=i);
set test;
i=1;
do while(i<=length(compress(put(O_IDO,best32.))));
O_ID=substr(compress(put(O_IDO,best32.)),i,5);
i+5;
output;
end;
run;

proc sort data=test1 ; by O_IDO; run;

proc transpose data=test1 out=test2(drop=_name_) prefix=O_OD;by O_IDO ; var O_ID; run;

Re: separating a string variable into different variables

dustychair — Mon, 25 Feb 2019 16:00:06 GMT

Hi @Patrick ,

Thank your for your response. Your code worked.

Sincerely,