02-18-2017 10:52 AM
02-18-2017 11:54 AM
I'm not aware of a way to make proc import read multiple lines as a single observation. So what you want do is make the multiple lines into single lines, when needed - then run proc import. For your '~' separated text file, assume you know in advance that you have 26 data fields (i.e. 25 '~') per record, and that no complete record needs more than 500 characters:
filename tmp temp; data _null_; infile 'c:\temp\t.tilde'; length outtxt $500; do i=1 by 1 while (countc(outtxt,'~')<25); input; outtxt=catx(' ',outtxt,_infile_); end; file tmp; put outtxt; run; proc import datafile=tmp out=want dbms=dlm replace; delimiter='~'; getnames=yes; run;
If you tried the proc import against the original file, you'd have problems with multiline records.
02-18-2017 01:11 PM
Thanks a lot for your help.
It given me a correct output however i am having little hard time in understanding the do loop logic in your code. Can you please explain it in detail for my understanding.
Like how this is working with _infile_
02-18-2017 01:23 PM
Here is a tested code, less elegant than @mkeintz but it gives the desired result:
filename text_in '/folders/myshortcuts/My_Folders/flat/sample_Survey.txt'; data text; retain confirmation_number first_name last_name exam_code form_code site_code testdate v1-v9 survey01-survey09 obs; length survey09 $200; format testdate date9.; infile text_in truncover firstobs=2 dlm='~' dsd eof=done; input confirmation $16. @; obs+1; if input(confirmation,?? 16.) > 0 then do; if obs > 1 then output; input @1 confirmation_number first_name $ last_name $ exam_code $ form_code $ site_code $ testdate mmddyy10. @+1 v1 $ v2 $ v3 $ v4 $ v5 $ v6 $ v7 $ v8 $ v9 $ survey01 $ survey02 $ survey03 $ survey04 $ survey05 $ survey06 $ survey07 $ survey08 $ survey09 $ ; end; else do; input @1 a_line $200.; survey09 = catx(' ',survey09,scan(a_line,1,'~')); end; drop obs confirmation a_line; return; done: output; run;
02-18-2017 05:38 PM
This do loop works as follows:
do i=1 by 1 while (countc(outtxt,'~')<25); input; outtxt=catx(' ',outtxt,_infile_); end;