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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.