topic Re: Separate data from one column to multiple columns in SAS Programming

Separate data from one column to multiple columns

Yashponde — Tue, 04 Jun 2019 05:55:23 GMT

I want to separate the above column into different columns as given below

Nettype Mesh Dimension

Manta 333 1*0.17

Re: Separate data from one column to multiple columns

anushreebiotech — Tue, 04 Jun 2019 05:58:11 GMT

Hi,

data have;
input path $50.;
datalines;
Manta/333/1*0.17
Manta/333/1*0.17
Manta/333/1*0.17
Manta/333/1*0.17
;
run;

data want;
set have;
Nettype= scan(path,1,'/');
Mesh= scan(path,2,'/');
Dimension= scan(path,3,'/');
run;

Regards,

Anushree

Re: Separate data from one column to multiple columns

andreas_lds — Tue, 04 Jun 2019 09:39:01 GMT

Using the scan-function multiple-times, as suggested by @anushreebiotech, is the easiest way solve the issue. You should use the length-statement to ensure that the variable Nettype, Mesh and Dimension are long enough to store the parts extracted by scan.

Re: Separate data from one column to multiple columns

Kurt_Bremser — Tue, 04 Jun 2019 11:21:12 GMT

Nice solution. Simple and works.

Just take care of Maxim 47 and set explicit lengths for the results, either to avoid waste of space or truncation.

Re: Separate data from one column to multiple columns

mansour_ib_sas — Tue, 04 Jun 2019 11:34:38 GMT

Hello,

I propose this solution:

data test;
var='Manta/333/1*0.17'; output;
var='Manta/ /1*0.17'; output;
var='Manta/333'; output;
var=' /Manta/333'; output;
; run;

data test1(drop=var i);
set test;
array vr(3) $ Nettype Mesh Dimension;
do i=1 to (countw(var,'/') );
vr(i)=scan(var,i,'/');
end;
run;