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

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

mikesatriaevo — Tue, 12 Jun 2012 01:53:46 GMT

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

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

PGStats — Tue, 12 Jun 2012 03:02:02 GMT

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;

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

mikesatriaevo — Tue, 12 Jun 2012 08:21:05 GMT

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?

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

PGStats — Tue, 12 Jun 2012 18:23:51 GMT

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;

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

mikesatriaevo — Wed, 13 Jun 2012 09:49:17 GMT

Hi PG,

It works like a charm. Thanks...