topic Re: Extract data from text file in SAS Procedures

Extract data from text file

hexx18 — Thu, 16 Jun 2016 19:42:33 GMT

Hi ,

I have an a messy data in text file like below and i have attached a text file and now i need to extract Credit acct names

IRA FLEXIBLE SPENDING ACCOUNT, GIA FLEXIBLE , CCC FLEXIBLE and also their credit scores 7.5, 0.22,6.8 and while extracting when there are two same names like IRA FLEXIBLE SPENDING ACCOUNT we need to check for latest date from Curr Date : so i have extracted the value 7.5 for IRA FLEXIBLE SPENDING ACCOUNT and also if there is no credit score listed under

Credit acct name the we dont need to pull that . Can anyone pls help

The output should look like

Credit acct name	Credit score
IRA FLEXIBLE SPENDING ACCOUNT	7.5
GIA FLEXIBLE	0.22
CCC FLEXIBLE	6.8

--------------------------------------------------------------------------------------------------

Daily Report
Printed on 30MAY13 at EST 07:19:00
Page 1
Tran No R Company Name DB/CR Amount DB Curr Date/Time Appv'd
Tran ID N Payment Method Transfer Amount CR Curr Date/Time Prnt'd
Value Date FX Rate

Con Reading Rainbow
Credit acct name: IRA FLEXIBLE SPENDING ACCOUNT
Credit acct status : Active

Credit Score : 7.5
Field : N

Curr Date : 05/30/2013
Approval Date : 05/30/2013

* PENDING *
Debit account number: 3333333333
Credit bank aaa: 12345678
Credit acct number: 1111111111

2510002 BABY SUPERSTORE 175,000.00 DB USD 30MAY12 07:20:31

BSUPER001 N Fedwire Out Interface 30MAY13 08:20:40
08SEP11 * CONFIRMED *
Debit account number: 2222222222
Receiving bank aba: 234567898
Credit account number: 2222-1111111-7
Credit account name: IND OPSE

--------------------------------------------------------------------------------------------------

Re: Extract data from text file

Ksharp — Fri, 17 Jun 2016 01:21:53 GMT

data have;
infile '/folders/myfolders/test03.txt' ;
length name $ 80;
retain name;
input ;
if _infile_ =: 'Credit acct name' then name=scan(_infile_,-1,':');
 else if _infile_ =: 'Credit Score' then do;
  score=input(scan(_infile_,-1,' '),best.);output;
 end;
run;

Re: Extract data from text file

hexx18 — Fri, 17 Jun 2016 02:26:23 GMT

Hi ,

I have got the output expected but

i am getting it like this . how do i code so that i get only IRA FLEXIBLE SPENDING ACCOUNT with only 7.5 as that value is of the latest date

Thanks a lot

Credit acct name	Credit score
IRA FLEXIBLE SPENDING ACCOUNT	6.5
IRA FLEXIBLE SPENDING ACCOUNT	7.5
GIA FLEXIBLE	0.22
CCC FLEXIBLE	6.8

Re: Extract data from text file

Ksharp — Fri, 17 Jun 2016 02:38:55 GMT

OK.

Add one more code after that .

data have;
infile '/folders/myfolders/test03.txt' ;
length name $ 80;
retain name;
input ;
if _infile_ =: 'Credit acct name' then name=scan(_infile_,-1,':');
 else if _infile_ =: 'Credit Score' then do;
  score=input(scan(_infile_,-1,' '),best.);output;
 end;
run;

data want;
 set have;
 by name notsorted;
 if last.name;
run;

Re: Extract data from text file

hexx18 — Fri, 17 Jun 2016 02:40:58 GMT

So add code to extract Curr Date : 05/30/2012 for each credit acct name or credit score also from the file so that i can order by descending date and pick up first occurance ?

Re: Extract data from text file

Ksharp — Fri, 17 Jun 2016 02:49:51 GMT

Sorry. I posted wrong code. The code has been updated.

Re: Extract data from text file

hexx18 — Fri, 17 Jun 2016 03:02:23 GMT

Hi ,

Below code didnt change anything

data want;
 set have;
 by name notsorted;
 if last.name;
run;

i think we can sort by pulling the curr date field also ? . What are your thoughts ?

Re: Extract data from text file

Ksharp — Fri, 17 Jun 2016 03:13:32 GMT

That is weird. I got this.

As long as you make sure the date is ordered in text file ,you can use my code. Otherwise , as you said pull the date and proc sort.

Re: Extract data from text file

hexx18 — Fri, 17 Jun 2016 03:19:18 GMT

Hi Keshan,

Yes you are right but can add to the code on how to extract Curr date to the code .

Thanks for help

Re: Extract data from text file

Ksharp — Fri, 17 Jun 2016 03:38:17 GMT

Sure.

data have;
infile '/folders/myfolders/test03.txt' ;
length name $ 80;
retain name datetime;
format datetime datetime20.;
input ;
if left(_infile_)=:'Printed on' then 
 datetime=dhms(input(scan(_infile_,3,' '),date9.),0,0,input(scan(_infile_,-1,' '),time9.));

else if _infile_ =: 'Credit acct name' then name=scan(_infile_,-1,':');
 else if _infile_ =: 'Credit Score' then do;
  score=input(scan(_infile_,-1,' '),best.);output;
 end;
run;

Re: Extract data from text file

hexx18 — Fri, 17 Jun 2016 04:32:34 GMT

How does this part change when i use Curr Approved date : 05/30/2012 and this is the date format and at the right side

if left(_infile_)=:'Printed on' then 
 datetime=dhms(input(scan(_infile_,3,' '),date9.),0,0,input(scan(_infile_,-1,' '),time9.));

Page 1
Tran No R Company Name DB/CR Amount DB Curr Date/Time Appv'd
Tran ID N Payment Method Transfer Amount CR Curr Date/Time Prnt'd
Value Date FX Rate

Con Reading Rainbow
Credit acct name: IRA FLEXIBLE SPENDING ACCOUNT
Credit acct status : Active

Credit Score : 6.5
Field : N

Curr Approved date : 05/30/2012
Last rejected date : 05/30/2010

Re: Extract data from text file

Ksharp — Fri, 17 Jun 2016 06:04:13 GMT

OK. But Curr and Approved have two date, which one you want ?

data have;
infile '/folders/myfolders/test03.txt' ;
length name $ 80 ;
retain name score;
input ; 
if _infile_ =: 'Credit acct name' then name=scan(_infile_,-1,':');
 else if _infile_ =: 'Credit Score' then score=input(scan(_infile_,-1,' '),best.);
  else if left(_infile_)=:'Approval Date' then do;
     datetime =input(scan(_infile_,-1,' '),mmddyy10.);output;
  end;
 format datetime mmddyy10.;
run;

Re: Extract data from text file

hexx18 — Sat, 18 Jun 2016 01:35:19 GMT

I am so Sorry to extend post

for now i dont have data before Curr date so you are left aligning and doing -1 . What if i have date before Current date like below

Scoring Pricing Comapny Current Approved Date : 03/22/2010

Now how do we extract 03/22/2010 .

Re: Extract data from text file

Ksharp — Sat, 18 Jun 2016 06:42:45 GMT

Use something like this :



else if find(_infile_,'Current Approved Date') then do;
     datetime =input(scan(_infile_,-1,' '),mmddyy10.);output;
  end;