How to convert Text to SAS Dataset and group all the transaction together

[mikesatriaevo]

Hi,

I'm seeking advice on following case. I'm using BASE 9.1.3. I have the attached raw file in txt format.

How do I group all Team 1 together? Because when I import, the data all store on the same column.

A. Team 1

0116 241  Johor                  MSA IN            25    4%             .00      2213

                                            MSA CW            16    2%       14,100.00

                                            ASE INQ          600    1%             .00

How do I group all Team 2 together? Because sometimes, there could be a row “TERM OWNR LOCATION“ in between, but as long as no next line like “0135 241  Johor” wording appear, the following rows should be group as above rows.

B. Team 2

0135 241  Johor                                     MSA IN            128    4%             .00      3199

                                            CWD               550   27%      626,500.00

                                            TRC                22    1%      472,433.09

DATE PRINTED :30/11/2011 23:36:19                                                                  NON CRITICAL, CONFIDENTIAL

ATM/CDM TERMINAL USAGE, BY TRX         BRANCH:000                                                  PAGE 00002

                                                                                                   REPORT ID   : f456987

TERM OWNR LOCATION                         TRN               CNT                AMOUNT       TOTAL

                                            TAC                 1     %             .00

[PGStats]

Use the flexibility of the PRX functions (were already available in 9.1.3), like this :

data test;

retain TERM OWNR LOCATION;

length TERM $4 OWNR $3 LOCATION $32 TRN $16;

infile "&SASForum\datasets\sample.txt" truncover;

input @;

if prxmatch("/^ \d{4}? \d{3}?  /",_infile_) then do;

     input term ownr location & TRN & CNT :dollar8. AMOUNT :percent4. TOTAL :dollar14.2;

     output;

     end;

else if prxmatch ("/^\s{44}?\w/", _infile_) then do;

     input TRN & CNT :dollar8. AMOUNT :percent4. TOTAL :dollar14.2;

     output;

     end;

run;

PG

[mikesatriaevo]

HI PG,

Thanks for your help. I'm a bit new and not that familiar with PRX function. Based on the code, it work find and i'm really appreciate. I have another scenario as attached in Sample 2, whereby when the word

“BRANCH” appears, then I would want to remove these rows under branch, until the next 4 digits code is detected.

How do i embed this to the code?

[PGStats · Accepted solution]

Add a flag (BRANCH) :

data test;

length TERM $4 OWNR $3 LOCATION $32 TRN $16;

retain TERM OWNR LOCATION;

retain BRANCH (0);

infile "&SASForum\datasets\sample2.txt" truncover;

input @;

BRANCH = BRANCH or scan(_infile_,1) = "BRANCH";

if prxmatch("/^ \d{4}? \d{3}?  /",_infile_) then do;

     input term ownr location & TRN & CNT :dollar8. AMOUNT :percent4. TOTAL :dollar14.2;

     output;

     BRANCH = 0;

     end;

else if prxmatch ("/^\s{44}?\w/", _infile_) then do;

     input TRN & CNT :dollar8. AMOUNT :percent4. TOTAL :dollar14.2;

     if not BRANCH then output;

     end;

run;

PG

[mikesatriaevo]

Hi PG,

It works like a charm. Thanks...