BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mikesatriaevo
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

PG

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

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

PG
mikesatriaevo
Fluorite | Level 6

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
Opal | Level 21

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

PG
mikesatriaevo
Fluorite | Level 6

Hi PG,

It works like a charm. Thanks...

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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