BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cybharg
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
art297
Opal | Level 21

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
Fluorite | Level 6
Thank you!
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 41006 views
  • 6 likes
  • 3 in conversation