BookmarkSubscribeRSS Feed
jayantyad
Fluorite | Level 6

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

7 REPLIES 7
Kurt_Bremser
Super User
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;
Aman4SAS
Obsidian | Level 7

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

Aman4SAS
Obsidian | Level 7

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;

Ksharp
Super User
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;
Peter_C
Rhodochrosite | Level 12
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!
Ksharp
Super User

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 ?

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2788 views
  • 5 likes
  • 5 in conversation