BookmarkSubscribeRSS Feed
rohini05
Calcite | Level 5

Hi

 

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:

Column1Column2Column3
@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.

4 REPLIES 4
s_lassen
Meteorite | Level 14

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;
rohini05
Calcite | Level 5
Thanks a ton! I will apply the same at my work tomorrow.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2035 views
  • 0 likes
  • 4 in conversation