Extract data from text file

[hexx18]

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

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

[Ksharp]

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;

[hexx18]

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

[Ksharp]

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;

[hexx18]

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 ? 

[Ksharp]

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

[hexx18]

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 ? 

[Ksharp]

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.

[hexx18]

Hi Keshan,

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

Thanks for help 

[Ksharp]

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;

[hexx18]

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

[Ksharp · Accepted solution]

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;

[hexx18]

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 . 

[Ksharp]

Use something like this :



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