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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.