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,935

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

Posted in reply to mikesatriaevo

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,935

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

Posted in reply to mikesatriaevo

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,935

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

Posted in reply to mikesatriaevo

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 and locked.

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

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