DATA Step, Macro, Functions and more

importing non-standard text file

Accepted Solution Solved
Reply
Highlighted
Contributor QLi
Contributor
Posts: 59
Accepted Solution

importing non-standard text file

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

Accepted Solutions
Solution
Thursday
Respected Advisor
Posts: 4,130

Re: importing non-standard text file

@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;

View solution in original post


All Replies
PROC Star
Posts: 1,669

Re: importing non-standard text file

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

 

Contributor QLi
Contributor
Posts: 59

Re: importing non-standard text file

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

Solution
Thursday
Respected Advisor
Posts: 4,130

Re: importing non-standard text file

@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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 78 views
  • 0 likes
  • 3 in conversation