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
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
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
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?
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
Hi PG,
It works like a charm. Thanks...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.