hello,
I need to import non-standard text file into SAS. anyone know how to do it? below is one data sample
Have : | ||||||
ID | Name_addess | RLT | MGR_NM | |||
1882 | DONNA O ABBOTT | 29791 | JONATHAN D COLLINS | |||
105 WINDWARD PSGE | ||||||
KNIGHTDALE NC 27545-9406 | ||||||
Want: | ||||||
ID | Name_addess_1 | Name_addess_2 | Name_addess_3 | RLT | MGR_NM | |
1882 | DONNA O ABBOTT | 105 WINDWARD PSGE | KNIGHTDALE NC 27545-9406 | 29791 | JONATHAN D COLLINS |
Below code should also handle cases where your source data doesn't always have 3 line address blocks and can also contain blank lines between the blocks.
filename raw temp;
data _null_;
file raw;
put 'ID,Name_addess,RLT,MGR_NM' ;
put '1882' @10 'DONNA O ABBOTT' @40 '29791' @50 'JONATHAN D COLLINS' ;
put @10 '105 WINDWARD PSGE';
put @10 'KNIGHTDALE NC 27545-9406';
put '9999' @10 'DONNA O ABBOTT' @40 '29791' @50 'JONATHAN D COLLINS' ;
put @10 '105 WINDWARD PSGE';
put;
put;
put '1882' @10 'DONNA O ABBOTT' @40 '29791' @50 'JONATHAN D COLLINS' ;
put ;
put @10 'KNIGHTDALE NC 27545-9406';
run;
data want(drop=_:);
infile raw firstobs=2 truncover;
input ID $1-9 @;
if not missing(id) then
do;
input Name_addess_1 $10-39 RLT $40-49 MGR_NM $50-70;
array nad {*} $30 Name_addess_2 Name_addess_3;
do _i=1 to dim(nad);
input _idNext $1-9 @@;
if missing(_idNext) then
do;
input nad[_i] $10-39;
end;
else leave;
end;
end;
if not missing(id) then output;
run;
The data you posted looks nothing like a text file.
Why do you want us to guess what your data looks like?
Supposing it is as below, this works:
data _null_;
file "%sysfunc(pathname(work))\test.txt";
put 'ID,Name_addess,RLT,MGR_NM' ;
put '1882, DONNA O ABBOTT, 29791, JONATHAN D COLLINS' ;
put '105 WINDWARD PSGE';
put 'KNIGHTDALE NC 27545-9406';
put '1882, DONNA O ABBOTT, 29791, JONATHAN D COLLINS' ;
put '105 WINDWARD PSGE';
put 'KNIGHTDALE NC 27545';
data WANT;
infile "%sysfunc(pathname(work))\test.txt" dlm=',' firstobs=2 pad ;
input ID $ ADD1 : $50. RLT $ MGR_NM $50.;
input ADD2 $50.;
input ADD3 $50.;
run;
ID | ADD1 | RLT | MGR_NM | ADD2 | ADD3 |
---|---|---|---|---|---|
1882 | DONNA O ABBOTT | 29791 | JONATHAN D COLLINS | 105 WINDWARD PSGE | KNIGHTDALE NC 27545-9406 |
1882 | DONNA O ABBOTT | 29791 | JONATHAN D COLLINS | 105 WINDWARD PSGE | KNIGHTDALE NC 27545 |
Chris, thanks. based on your codes, i fix it out. By using multiple INPUT statements.
Below code should also handle cases where your source data doesn't always have 3 line address blocks and can also contain blank lines between the blocks.
filename raw temp;
data _null_;
file raw;
put 'ID,Name_addess,RLT,MGR_NM' ;
put '1882' @10 'DONNA O ABBOTT' @40 '29791' @50 'JONATHAN D COLLINS' ;
put @10 '105 WINDWARD PSGE';
put @10 'KNIGHTDALE NC 27545-9406';
put '9999' @10 'DONNA O ABBOTT' @40 '29791' @50 'JONATHAN D COLLINS' ;
put @10 '105 WINDWARD PSGE';
put;
put;
put '1882' @10 'DONNA O ABBOTT' @40 '29791' @50 'JONATHAN D COLLINS' ;
put ;
put @10 'KNIGHTDALE NC 27545-9406';
run;
data want(drop=_:);
infile raw firstobs=2 truncover;
input ID $1-9 @;
if not missing(id) then
do;
input Name_addess_1 $10-39 RLT $40-49 MGR_NM $50-70;
array nad {*} $30 Name_addess_2 Name_addess_3;
do _i=1 to dim(nad);
input _idNext $1-9 @@;
if missing(_idNext) then
do;
input nad[_i] $10-39;
end;
else leave;
end;
end;
if not missing(id) then output;
run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.