Help using Base SAS procedures

Splitting a delimited column into multiple columns

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Splitting a delimited column into multiple columns

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

 


Accepted Solutions
Solution
‎04-19-2017 12:51 AM
PROC Star
Posts: 7,416

Re: Splitting a delimited column into multiple columns

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

View solution in original post


All Replies
Solution
‎04-19-2017 12:51 AM
PROC Star
Posts: 7,416

Re: Splitting a delimited column into multiple columns

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

New Contributor
Posts: 2

Re: Splitting a delimited column into multiple columns

Thank you!
Super User
Super User
Posts: 7,565

Re: Splitting a delimited column into multiple columns

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 344 views
  • 3 likes
  • 3 in conversation