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
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;
There is one limitation if words in name will vary i.e. 2 , 3 and 4 words in that case it will not work.
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.
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;
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,
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 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.