topic Re: Extract data from messy text file in SAS Programming

Extract data from messy text file

rohini05 — Thu, 31 Jan 2019 07:54:36 GMT

I really need help asap with one of the queries related to sas data extract.

Background:I have one input text file that contains data related to transactions which is spread across multiple lines having no delimiters.

Basically in first column I want text starting with @ until end of line;

in second column text starting with :50:H until :21:

in third column text stating with :21: until:71A: This will repeat until all :21: are considered in expected outcome

Sample data:

@20111819
:20:BFSC
:28:DABC
:50H:/191919
Amazon
Sydney
Australia
:21:AU111
:23E:ABC
:24C:DEF
:71A:April
:21:AU222
:23E:ABC
:24C:DEF
:71A:April
:21:AU333
:23E:ABC
:24C:DEF
:71A:April
@20111820
:20:BFSC
:28:DABC
:50H:/191919
Amazon2
Sydney
Australia
:21:AU111
:23E:ABC
:24C:DEF
:71A:April
:21:AU222
:23E:ABC
:24C:DEF
:71A:April
:21:AU333
:23E:ABC
:24C:DEF
:71A:April

Expected Output:

Column1	Column2	Column3
@20111819	:50H:/191919 Amazon Sydney Australia	:21:AU111 :23E:ABC :24C:DEF :71A:April
@20111819	:50H:/191919 Amazon Sydney Australia	:21:AU222 :23E:ABC :24C:DEF :71A:April
@20111819	:50H:/191919 Amazon Sydney Australia	:21:AU333 :23E:ABC :24C:DEF :71A:April
@20111820	:50H:/191919 Amazon2 Sydney Australia	:21:AU111 :23E:ABC :24C:DEF :71A:April
@20111820	:50H:/191919 Amazon2 Sydney Australia	:21:AU222 :23E:ABC :24C:DEF :71A:April
@20111820	:50H:/191919 Amazon2 Sydney Australia	:21:AU333 :23E:ABC :24C:DEF :71A:April

Can someone please help me with this.

Re: Extract data from messy text file

Kurt_Bremser — Thu, 31 Jan 2019 08:18:51 GMT

Pleas use the {i} button for posting example text; the main posting window scrambles your data (as seen by the emoticons).

Re: Extract data from messy text file

s_lassen — Thu, 31 Jan 2019 08:28:29 GMT

Something like this?

data want;
  array columns(3) $100 column1-column3;
  keep column1-column3;
  do until(0);
    infile cards eof=done;
    input;
    if _infile_=:'@' then do;
      column1=_infile_;
      colno=.;
      call missing(column2,column3);
      end;
    else do;
      if _infile_=:':50H' then
        colno=2;
      else if _infile_=:':21:' then
        colno=3;
      if not missing(colno) then
        call catx(' ',columns(colno),_infile_);
      if _infile_=:':71A:' then do;
        output;
        call missing(column3);
        end;
      end;
    end;
done:
cards;
@20111819
:20:BFSC
:28ABC
:50H:/191919
Amazon
Sydney
Australia
:21:AU111
:23E:ABC
:24CEF
:71A:April
:21:AU222
:23E:ABC
:24CEF
:71A:April
:21:AU333
:23E:ABC
:24CEF
:71A:April
@20111820
:20:BFSC
:28ABC
:50H:/191919
Amazon2
Sydney
Australia
:21:AU111
:23E:ABC
:24CEF
:71A:April
:21:AU222
:23E:ABC
:24CEF
:71A:April
:21:AU333
:23E:ABC
:24CEF
:71A:April
;run;

Re: Extract data from messy text file

RW9 — Thu, 31 Jan 2019 08:32:38 GMT

Something like:

data want;
  length line $2000;
  infile "&_sasws_./temp.txt" dlm="¬" end=lastrec;
  input line $;
  line=strip(line);
  array vs{3} $2000;
  retain vs: i;
  if _n_=1 then i=1;
  else if char(line,1)="@" then do;
    output;
    i=1;
    call missing(of vs{*});
    vs{i}=line;
  end;
  else if substr(line,1,3)=":50" then i=2;
  else if substr(line,1,3)=":21" then i=3;
  vs{i}=cats(vs{i},line);
  if lastrec then output;
run;

Re: Extract data from messy text file

rohini05 — Thu, 31 Jan 2019 09:06:21 GMT

Thanks a ton! I will apply the same at my work tomorrow.