Help using Base SAS procedures

Extract data from text file

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Extract data from text file

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 ACCOUNT7.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

 

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


Accepted Solutions
Solution
‎06-17-2016 01:37 PM
Super User
Posts: 10,028

Re: Extract data from text file

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;

View solution in original post


All Replies
Super User
Posts: 10,028

Re: Extract data from text file

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;
Contributor
Posts: 22

Re: Extract data from text file

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 ACCOUNT6.5
IRA FLEXIBLE SPENDING ACCOUNT7.5
GIA FLEXIBLE 0.22
CCC FLEXIBLE 6.8
Super User
Posts: 10,028

Re: Extract data from text file

[ Edited ]

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;

 

Contributor
Posts: 22

Re: Extract data from text file

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 ? 

 

 

 

Super User
Posts: 10,028

Re: Extract data from text file

Sorry. I posted wrong code. The code has been updated.
Contributor
Posts: 22

Re: Extract data from text file

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 ? 

Super User
Posts: 10,028

Re: Extract data from text file

That is weird. I got this.

x.png

 

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.

Contributor
Posts: 22

Re: Extract data from text file

Hi Keshan,

 

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

 

 

 

 

Thanks for help 

Super User
Posts: 10,028

Re: Extract data from text file

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;
Contributor
Posts: 22

Re: Extract data from text file

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

Solution
‎06-17-2016 01:37 PM
Super User
Posts: 10,028

Re: Extract data from text file

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;
Contributor
Posts: 22

Re: Extract data from text file

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 . 

Super User
Posts: 10,028

Re: Extract data from text file

Use something like this :



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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 799 views
  • 4 likes
  • 2 in conversation