BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dustychair
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

 

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20
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;
34reqrwe
Quartz | Level 8

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 . 

novinosrin
Tourmaline | Level 20

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;
novinosrin
Tourmaline | Level 20

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;
Satish_Parida
Lapis Lazuli | Level 10

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.

Ksharp
Super User
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;
Patrick
Opal | Level 21

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;

 

mansour_ib_sas
Pyrite | Level 9

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; 
dustychair
Pyrite | Level 9

Hi @Patrick ,

Thank your for your response. Your code worked.

 

Sincerely,

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1141 views
  • 5 likes
  • 7 in conversation