BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hexx18
Quartz | Level 8

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

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

13 REPLIES 13
Ksharp
Super User
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
Quartz | Level 8

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
Ksharp
Super User

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
Quartz | Level 8

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
Super User
Sorry. I posted wrong code. The code has been updated.
hexx18
Quartz | Level 8

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
Super User

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.

hexx18
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User
Use something like this :



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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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