Splitting a delimited column into multiple columns

[cybharg]

Hi,

Please help..I have a SAS dataset that contains all the variables in one column separated by pipe delimiter for different columns.

Sample:

Row
-------------------
ABC|2015|XYZ
ABC||XYZ

I'm using the following code to split it into different variables.

data split;
   set test;
   length var1-var3 $10.;
   array var(3) $;
   do i = 1 to dim(var);
      var[i]=scan(row,i,'|');
   end;
run;

This code runs fine if all the columns have data. But if any of them are empty like the second row in the sample above, it outputs:

var1 var2  var3
-------------------
ABC  2015  XYZ
ABC  XYZ

I need it to not treat consecutive delimiters as one. The output should be

var1 var2  var3
----------------
ABC  2015   XYZ
ABC         XYZ

[art297 · Accepted solution]

Include the M modifier in your use of the scan function. i.e.,

data split;
   set test;
   length var1-var3 $10.;
   array var(3) $;
   do i = 1 to dim(var);
      var[i]=scan(row,i,'|','M');
   end;
run;

Art, CEO, AnalystFinder.com

[cybharg]

Thank you!

[RW9]

Why does you data appear all in one column with a delimiter, it sounds like your previous step to import the data is not working.  Fix your import program to correctly read in the delimited data and format it correctly.   The way you are "fixing" it here in code means that a numeric variable - year - will actually be character which may make working with it more difficult than needs to be.  To import the data correctly use a datastep:

data want;
  infile datalines dlm="|" dsd;
  input var1 $ var2 var3 $;
datalines;
ABC|2015|XYZ
ABC||XYZ
;
run;

Of course this is a simple example (and you would replace datalines with your filename of course), you could also apply formats, informats on how to read the data (for dates etc.).