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
--------------------------------------------------------------------------------------------------
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;
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;
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 |
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;
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 ?
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 ?
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.
Hi Keshan,
Yes you are right but can add to the code on how to extract Curr date to the code .
Thanks for help
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;
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
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;
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 .
Use something like this : else if find(_infile_,'Current Approved Date') then do; datetime =input(scan(_infile_,-1,' '),mmddyy10.);output; end;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: