DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

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

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

Attachment
Attachment

Accepted Solutions
Solution
‎06-12-2012 02:23 PM
Respected Advisor
Posts: 4,659

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

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 Smiley Tongueercent4. TOTAL :dollar14.2;

     output;

     BRANCH = 0;

     end;

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

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

     if not BRANCH then output;

     end;

run;

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,659

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

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 Smiley Tongueercent4. TOTAL :dollar14.2;

     output;

     end;

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

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

     output;

     end;

run;

PG

PG
Contributor
Posts: 21

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

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?

Solution
‎06-12-2012 02:23 PM
Respected Advisor
Posts: 4,659

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

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 Smiley Tongueercent4. TOTAL :dollar14.2;

     output;

     BRANCH = 0;

     end;

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

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

     if not BRANCH then output;

     end;

run;

PG

PG
Contributor
Posts: 21

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

Hi PG,

It works like a charm. Thanks...

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 329 views
  • 3 likes
  • 2 in conversation