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:
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.
Pleas use the {i} button for posting example text; the main posting window scrambles your data (as seen by the emoticons).
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.