DATA Step, Macro, Functions and more

How to read Raw data with embedded blank without increasing the space between two values?

Reply
Occasional Contributor
Posts: 5

How to read Raw data with embedded blank without increasing the space between two values?

I have the following Raw data:

1023 David Shaw red 189 165
1049 Amelia Serrano yellow 145 124   
1219 Alan Nance red 210 192  
1246 Ravi Sinha yellow 194 177   
1078 Ashley McKnight red 127 118   
1221 Jim Brown yellow 220 .

 

2nd and 3rd value have to be read together as they are the part of single variable:

e.g. Full_name: David Shaw

Full_name: Amelia Serrano

 

The above mentioned Data do not have more then one space between any values.

 

One method to read this type of data is to use modifier '&' that changes the default delimiter from single space to double space.

One limitation of the above mentioned method is that the space between the value and the next value has to be 2 or more for succesful execution.

 

However In this case we cannot increase the space between any of the value.

Please provide a method to read this type of Data.

 

Any help would be appreciated

 

SAS version 9.2

Super User
Posts: 6,928

Re: How to read Raw data with embedded blank without increasing the space between two values?

data want;
length
  id 8
  name1 name2 $10
  name $20
  colour $7
;
input
  id
  name1
  name2
  colour
  num1
  num2
;
name = catx(' ',name1,name2);
drop name1 name2;
cards;
1023 David Shaw red 189 165
1049 Amelia Serrano yellow 145 124   
1219 Alan Nance red 210 192  
1246 Ravi Sinha yellow 194 177   
1078 Ashley McKnight red 127 118   
1221 Jim Brown yellow 220 .
;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 265

Re: How to read Raw data with embedded blank without increasing the space between two values?

There is one limitation if words in name will vary i.e. 2 , 3 and 4 words in that case it will not work.

Super User
Posts: 6,928

Re: How to read Raw data with embedded blank without increasing the space between two values?

Then one needs to countw the _infile_ to determine the number of of words for the name, and assign the variables with input(scan(_infile,,),) accordingly.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 265

Re: How to read Raw data with embedded blank without increasing the space between two values?

data test;
infile cards truncover;
input x $200.;
cards;
1023 David Shaw red 189 165
1049 Amelia Serrano yellow 145 124
1219 Alan Nance red 210 192
1246 Ravi Sinha yellow 194 177
1078 Ashley McKnight red 127 118
1221 Jim Brown yellow 220 .
;
run;

data test2;
set test;
id=scan(x,1);
name=catx(" ",scan(x,2),scan(x,3));
color=scan(x,4);
no1=scan(x,5);
no2=scan(x,6);
drop x;
run;

Super User
Posts: 9,671

Re: How to read Raw data with embedded blank without increasing the space between two values?

data want;
length id name colour  num1 num2 $ 200;
input;
  id=scan(_infile_,1,' ');
  colour=scan(_infile_,-3,' ');
  num1=scan(_infile_,-2,' ');
  num2=scan(_infile_,-1,' ');

call scan(_infile_,2,p1,l1,' ');
call scan(_infile_,-3,p2,l2,' ');
name=substr(_infile_,p1,p2-p1);
drop p1 p2 l1 l2;
cards;
1023 David Shaw red 189 165
1049 Amelia Serrano yellow 145 124   
1219 Alan Nance red 210 192  
1246 Ravi Sinha yellow 194 177   
1078 Ashley McKnight red 127 118   
1221 Jim Brown yellow 220 .
;
run;
Valued Guide
Posts: 2,174

Re: How to read Raw data with embedded blank without increasing the space between two values?

As long as there a fixed mumber of "words" around the part with a variable number of words, the CALL SCAN() solution offered by @Ksharp answers the problem!
Super User
Posts: 9,671

Re: How to read Raw data with embedded blank without increasing the space between two values?

Peter.C,

Of course. If one of num1 and num2 is missing( a blank ), How do you know it is belong to num1 or num2 ?

Ask a Question
Discussion stats
  • 7 replies
  • 355 views
  • 5 likes
  • 5 in conversation