topic Re: importing non-standard text file in SAS Programming

importing non-standard text file

QLi — Fri, 13 Oct 2017 00:47:47 GMT

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

Re: importing non-standard text file

ChrisNZ — Fri, 13 Oct 2017 01:12:33 GMT

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

Re: importing non-standard text file

QLi — Fri, 13 Oct 2017 01:58:58 GMT

Chris, thanks. based on your codes, i fix it out. By using multiple INPUT statements.

Re: importing non-standard text file

Patrick — Fri, 13 Oct 2017 02:20:03 GMT

@QLi

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;