topic Re: Split columns by positions in SAS Programming

Split columns by positions

Daniele96 — Tue, 22 Feb 2022 09:30:19 GMT

Hi everybody,
I am a new SAS user and I'd need your help.
I have a dataset having a single variable with multiple rows in each of which a string is contained.
This last must be divided according to a given positions and length, this data (positions and length) are contained in another dataset.

Example:
Dataset containing positions and length:

Length	Position
2	1
1	3
8	4


Dataset to be divided:

VAR1

abc0123def0

kol1256oiu1

The solution I found is to use the substr function for each variable, writing manually the positions and length:

data output; set input; var_1=substr(VAR1, 1, 2); var_1=substr(VAR1, 3, 1); var_1=substr(VAR1, 4, 8); drop VAR1; run;

but this method is slow, is there a faster and more automatic method? 
Thank you 🙂

Re: Split columns by positions

PeterClemmensen — Tue, 22 Feb 2022 09:41:40 GMT

Do you want this to be split into separate variables or observations ?

Re: Split columns by positions

PeterClemmensen — Tue, 22 Feb 2022 09:49:34 GMT

If observations :

data one;
input Length Position;
datalines;
2 1 
1 3 
8 4 
;

data two;
input VAR1 :$12.;
datalines;
abc0123def0 
kol1256oiu1 
;
 
data want(keep = var2);
   set two;

   do i = 1 to n;
      set one point = i nobs = n;
	  var2 = substr(var1, Position, Length);
	  output;
   end;
run;

Re: Split columns by positions

Daniele96 — Tue, 22 Feb 2022 10:11:53 GMT

Thanks for the reply, I'd be interested in spitting the dataset into separate variables.

Re: Split columns by positions

PeterClemmensen — Tue, 22 Feb 2022 10:23:46 GMT

Ok. Is it always three variables? Or can that vary?

Re: Split columns by positions

Daniele96 — Tue, 22 Feb 2022 10:27:29 GMT

It can vary.

Re: Split columns by positions

PeterClemmensen — Tue, 22 Feb 2022 10:39:48 GMT

Ok. Try this

data one;
input Length Position;
datalines;
2 1 
1 3 
8 4 
;

data two;
input var1 :$12.;
datalines;
abc0123def0 
kol1256oiu1 
;
 
proc sql noprint;
   select count(*) into :d separated by ''
   from one;
quit;

data want(keep = var:);
   set two;

   v = Var1;

   do i = 1 to n;
      set one point = i nobs = n;
	  array var {&d.}; 
	  var[i] = substr(v, Position, Length);
   end;
run;

Re: Split columns by positions

Daniele96 — Tue, 22 Feb 2022 11:13:08 GMT

WOW this is great!!! This code works on my original data, thank you 😊

Re: Split columns by positions

PeterClemmensen — Tue, 22 Feb 2022 11:34:51 GMT

Anytime, glad to help 🙂