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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: