topic Re: How to read delimiter if it's a multiple spaces in SAS Programming

How to read delimiter if it's a multiple spaces

iSAS — Wed, 01 Jul 2020 12:11:33 GMT

I have a data that uses multiple spaces (3 spaces) as a delimiter:

data have;
input text&$100.;
cards;
Last_Name, First_Name Middle_Name 123456789 Company NameX;
data want;
set have;
want=scan(text,1,' ');
run;

I used the scan function and specify the 3 spaces for my delimiter but it doesn't work. I could have used COMPBL function to get rid of the multiple spaces but my issue on that is Full Name and Company Name have spaces(single space) as well so they will be seperated

Re: How to read delimiter if it's a multiple spaces

PaigeMiller — Wed, 01 Jul 2020 12:15:44 GMT

What 3 spaces are you referring to? You didn't provide any actual data

Re: How to read delimiter if it's a multiple spaces

iSAS — Wed, 01 Jul 2020 12:19:29 GMT

Thank you for your time. This is the sample:
Last_Name, First_Name Middle_Name 123456789 Company NameX;
I even used this function to get the Company_Name but it doesn't work also:
Company_Name=scan(have,-1,index(have,' '));

Re: How to read delimiter if it's a multiple spaces

PaigeMiller — Wed, 01 Jul 2020 12:25:34 GMT

There are no multiple spaces in this sample data. So I still don't understand the question.

Re: How to read delimiter if it's a multiple spaces

iSAS — Wed, 01 Jul 2020 12:30:42 GMT

Last_Name, First_Name Middle_Nam**with 3 spaces**123456789**3 spaces**Company NameX;

Re: How to read delimiter if it's a multiple spaces

gamotte — Wed, 01 Jul 2020 12:40:14 GMT

Hello,

Please provide example data so we can better understand your problem.

Are you looking for something like this (dlmstr option)?

filename a temp;

data _NULL_;
file a;
put "foo   b  ar   ba z";
run;

data have;
length x y z $20.;
infile a dlmstr="   ";
input x y z ;
run;

Re: How to read delimiter if it's a multiple spaces

Kurt_Bremser — Wed, 01 Jul 2020 12:52:15 GMT

It also works with datalines:

data have;
length x y z $20.;
infile datalines dlmstr="   ";
input x y z;
datalines;
foo   b  ar   ba z
;

😉

Re: How to read delimiter if it's a multiple spaces

gamotte — Wed, 01 Jul 2020 13:01:14 GMT

Yes, i used a temporary file as i suspect that @iSAS data come in the form of an external file but i may be wrong.

Re: How to read delimiter if it's a multiple spaces

Kurt_Bremser — Wed, 01 Jul 2020 13:07:07 GMT

Posting code in the main posting window will cause text to be used in HTML, which means any sequence of white space is condensed into a single blank.

That's why it is an ABSOLUTE MUST to use the "little running man" button (right next to the one indicated in the picture) for code:

Re: How to read delimiter if it's a multiple spaces

iSAS — Wed, 01 Jul 2020 13:39:06 GMT

Thank you for your time KurtBremser. What if my data is already a sas dataset in 1 column, may I know how will I translate the given solution?

Re: How to read delimiter if it's a multiple spaces

Tom — Wed, 01 Jul 2020 13:51:15 GMT

Do you only have the text already in a dataset? Or are you reading the text from a file?

Does the data ever have 2 spaces that you don't want to treat as a delimiter?

If not then just the the & modifier on the INPUT statement and SAS will not use only a single space as a delimiter.

data want;
  length person $100 id $20 compary $50 ;
  input person &  id & company & ;
cards;
Last_Name, First_Name Middle_Name   123456789   Company NameX
;

proc print;
run;

Results:

Obs                 person                     id           company

 1     Last_Name, First_Name Middle_Name    123456789    Company NameX

If you only have the string variable then first convert the triple spaces to some character that is not in the data and use that in the SCAN() function.

data want;
  set have;
  length company $50;
  company=scan(tranwrd(text,'   ','FF'x),-1,'FF'x);
run;

Re: How to read delimiter if it's a multiple spaces

Kurt_Bremser — Wed, 01 Jul 2020 13:58:10 GMT

You can use @gamotte's "trick" and use a temporary file:

data have;
x1 = "aaa   bbb   ccc";
run;

filename a temp;

data _null_;
set have;
file a;
put x1;
run;

data h2;
infile a dlmstr="   ";
input x2 $ x3 $ x4 $;
run;

data want;
merge have h2;
run;

filename a clear;

Re: How to read delimiter if it's a multiple spaces

iSAS — Wed, 01 Jul 2020 14:50:44 GMT

Hello All,

Thanks to all who helped me! I encountered additional issue on my data but all your suggestions helped fix the issue. What i did was this:

data want;
set have (keep=FLEX_VALUE_1);
translate=tranwrd(FLEX_VALUE_1,' ','|'); /*The issue of this is all of the length of my data is 200*/
translate2=tranwrd(FLEX_VALUE_1,' ',''); /*This was created to get the actual length needed in translate variable. This wasn't used in the first place bec., after testing, it will have an issue on scan function. the issue has something to do with the data itself*/
/*numb=length(translate2); This was used for testing*/
value=substr(translate,1,length(translate2));/*To only get the needeed value of translate*/
NAme= SCAN(value, 1, '|');
Account = PUT(INPUT(SCAN(value, 2, '|'), best.) , z11.);
company = SCAN(value, -1, '|');
run;

Feel free to share your kowledge if you have suggestions to improve my code or if you see some errors so i could learn from you guys. thanks you so much!

Re: How to read delimiter if it's a multiple spaces

Tom — Wed, 01 Jul 2020 15:28:17 GMT

Not sure what the latest question is, but if you don't tell SAS explicitly how to define a variable it must make its own guess. So in your code the first place new variables TRANSLATE and TRANSLATE2 are seen is being assigned the result of a character function. So SAS will define them as length 200. You should define them first using a LENGTH statement. Or if for some reason you don't know the length you need you can trick SAS into defining them as being the same length as FLEX_VALUE_1 by adding extra assignment statements.

translate=FLEX_VALUE_1;
translate=tranwrd(FLEX_VALUE_1,'   ','|');