topic Help needed for writing code for resolving issue in SAS Data Management

Help needed for writing code for resolving issue

venkatnaveen — Mon, 27 Apr 2015 08:46:35 GMT

Hi I have credit card approve/decline tables with one table contains last 8months data, so i have an other table of this month who has appiled for credit card,so what is my requirement is i wanna check weather the customer has already have a card r not ,if he is having i wanna decline tht customer to approve new credit card,so what i want to do is i wanna check latest table (i.e this month table where customer applied for new credit card ,assume tht it contain 10 thousand custumer records) ,to last 8months table(assume tht it contain 1millon customer records),here i enclosing some sample table for example

/*assume tht its last 8months table which have 1million records*/

data table1;
input custid name$ salary pan$ aprvdate ;
informat aprv anydtdte.;
format aprv ddmmyy10.;
cards;
001 john 200000 AAAAAA 02/02/2014
002 raju 300000 BBBBBB 01/01/2013
002 raju 400002 BBBBBB 01/01/2014
003 david 600000 CCCCCC 04/04/2013
004 krishna 203030 DDDDDD 05/02/2015
005 mukesh 240000 EEEEEE 04/03/2014
003 david 782226 CCCCCC 08/05/2014
;
run;

/*assume that it contains current table whose wanna apply for new credit cards which contain 10thousand records*/

data table2;
input custid name$ salary pan$ aprv :anydtdte.;
format aprv ddmmyy10.;
cards;
002 raju 400000 BBBBBB 12/05/2015
005 mukesh 240000 EEEEEE 12/05/2015
003 david 782226 CCCCCC 01/7/2015
run;

Note :in table one the custmer raju and david had taken 2 cards ,they were already approved ,and check sal and date of approve ,for eg:raju taken cards on 01/01/2013 when his sal was 300000 again he applied for cards in 01/01/2014,again this card was approved and his sal aslo incresed,but in 12/05/2015 again he is applying for new credit cards,so i wanna check with previous table will this guy had already card/not,based on tht i wanna decline this card,and i wanna show my client reason why i m decline,i wanna result should be like this
002 raju 300000 BBBBBB 01/01/2015
002 raju 400002 BBBBBB 01/01/2015
002 raju 400000 BBBBBB 12/05/2015

so this i can show my client where he taken has taken card on 01/01/2013,again by mistakely we approved his card when he applied in 2014,so again this time he is applying for new card on 2015 ,so i gonna decline his application,even mukesh and david is already had card again he is applying in 2015 so i wanna decline him,
i wanna output like his

002 raju 300000 BBBBBB 01/01/2015
002 raju 400002 BBBBBB 01/01/2015
002 raju 400000 BBBBBB 12/05/2015
003 david 600000 CCCCCC 04/04/2013
003 david 782226 CCCCCC 08/05/2014
003 david 782226 CCCCCC 01/7/2015
005 mukesh 240000 EEEEEE 04/03/2014
005 mukesh 240000 EEEEEE 12/05/2015

so i wanna get output like this based on tht i can show my client these were the customer who had already got cards again they were applying,

Assume u have to compare 10thousand records with 1 milon records of previoud month,and pan number will be unique so based on tht we can catch up,

so plz write a code to resolve this kind of issue

Re: Help needed for writing code for resolving issue

RW9 — Mon, 27 Apr 2015 08:56:11 GMT

Create subsets of your data and update scripts. I.e. create a new table which just has distinct customer, last_date_of_application etc. Then compare your 10,000 records against the smaller table which only contains the data you need. Its a pretty simple merge then:

proc sql;

create table APPROVED as

select A.*

from PENDING A

left join ALREADY_DONE B

on A.CUST_ID=B.CUST_ID

where B.CUST_ID is null;

quit;

Re: Help needed for writing code for resolving issue

gsreddy — Tue, 28 Apr 2015 10:30:45 GMT

create table Decline as

select * from table1 where pan in (select pan from table2)

union all

select * from table2 where pan in (select pan from table1)

Re: Help needed for writing code for resolving issue

Patrick — Tue, 28 Apr 2015 11:08:50 GMT

If your tables are in SAS then using hash lookup tables could be quite efficient. In case your current month table could contain more than one record per customer then a tweak to below code would be required.

/*assume tht its last 8months table which have 1million records*/

data Last8Month;

input custid name$ salary pan$ aprv :anydtdte.;

format aprv ddmmyy10.;

cards;

001 john 200000 AAAAAA 02/02/2014

002 raju 300000 BBBBBB 01/01/2013

002 raju 400002 BBBBBB 01/01/2014

003 david 600000 CCCCCC 04/04/2013

004 krishna 203030 DDDDDD 05/02/2015

005 mukesh 240000 EEEEEE 04/03/2014

003 david 782226 CCCCCC 08/05/2014

;

run;

/*assume that it contains current table whose wanna apply for new credit cards which contain 10thousand records*/

data Current;

input custid name$ salary pan$ aprv :anydtdte.;

format aprv ddmmyy10.;

cards;

002 raju 400000 BBBBBB 12/05/2015

005 mukesh 240000 EEEEEE 12/05/2015

003 david 782226 CCCCCC 01/7/2015

;

run;

data _null_;

if _n_=1 then

do;

if 0 then set Last8Month current;

dcl hash h(dataset:'current');

_rc=h.defineKey('custid');

_rc=h.defineData(all:'y');

_rc=h.defineDone();

dcl hash declined(dataset:'current(obs=0)',ordered:'y',multidata:'n');

_rc=declined.defineKey('custid','aprv');

_rc=declined.defineData(all:'y');

_rc=declined.defineDone();

end;

set Last8Month end=last;

if h.check()=0 then

do;

_rc=declined.add();

_rc=h.find();

_rc=declined.add();

end;

if last then declined.output(dataset:'want');

run;

proc print data=want noobs;

run;