topic Re: Splitting a delimited column into multiple columns in SAS Procedures

Splitting a delimited column into multiple columns

cybharg — Wed, 19 Apr 2017 04:37:07 GMT

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

Re: Splitting a delimited column into multiple columns

art297 — Wed, 19 Apr 2017 04:45:00 GMT

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

Re: Splitting a delimited column into multiple columns

cybharg — Wed, 19 Apr 2017 04:51:57 GMT

Thank you!

Re: Splitting a delimited column into multiple columns

RW9 — Wed, 19 Apr 2017 08:12:37 GMT

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.).