I have a data set like
Agreementno serialno agreementdate amount intrest
ab10005 7456q9 10jan2012:00:00:00 150000 700
cd20006 6783q8 15feb2012:00:00:00 700000
bv40007 7672q5 07mar2011:00:00:00 80000
gu80005 7637q8 09mar2010:00:00:00 90000 900
I have to create a dataset by proc sql which contain colomn like Ageementno,col1,col2,col4,serialno,col5,col6,agreementdate,recciptno,amount,intrestt.
below calculation has been done in new dataset
recciptno=agreementno+last 2 digit of serialno+first 2 digit of agreement date+amount
intrest=if intrest is null then put 400 ,if intrest greater then 700 put 300
col6="dh"
example of required data as mention below
agreementno col1 col2 col4 serialno col5 col6 agreementdate recieptno amount intrest
ab10005 7456q9 dh 10jan2012:00:00:00 ab10005q910150000 150000 700
cd20006 6783q8 dh 15feb2012:00:00:00 cd20006q815700000 700000 400
bv40007 7672q5 dh 07mar2011:00:00:00 bv40007q50780000 80000 400
gu80005 7637q8 dh 09mar2011:00:00:00 gu80005q80990000 90000 300
Please help me to write the query in proc sql statement
Regards,
Ashwini
try this one.
data test;
informat agreementdate datetime18.;
format agreementdate datetime18.;
input Agreementno $ serialno $ agreementdate amount intrest;
cards;
ab10005 7456q9 10jan2012:00:00:00 150000 700
cd20006 6783q8 15feb2012:00:00:00 700000 .
bv40007 7672q5 07mar2011:00:00:00 80000 .
gu80005 7637q8 09mar2010:00:00:00 90000 900
;
run;
proc sql;
create table want as
select agreementno,
' ' as col1,' ' as col2,' ' as col4,
serialno,
' ' as col5,
'dh' as col6,
agreementdate,
trim(agreementno)||trim(substr(serialno,5,2))||trim(substr(put(datepart(agreementdate),ddmmyy10.),1,2))||trim((put(amount,8.))) as recieptno,
amount,
intrest
from test
;
quit;
data want;
set want;
if intrest eq . then intrest=400;
else if intrest gt 700 then intrest=300;
else intrest=intrest;
run;
regards,
vishnu
Ashwini, Modifying the above code gives a complete proc sql solution.
data test;
informat agreementdate datetime18.;
format agreementdate datetime18.;
input Agreementno $ serialno $ agreementdate amount interest;
cards;
ab10005 7456q9 10jan2012:00:00:00 150000 700
cd20006 6783q8 15feb2012:00:00:00 700000 .
bv40007 7672q5 07mar2011:00:00:00 80000 .
gu80005 7637q8 09mar2010:00:00:00 90000 300
;
run;
proc sql;
create table want as
select agreementno, '' as col1, '' as col2, '' as col4, serialno, '' as col5, 'dh' as col6, agreementdate,
cats(agreementno,substr(serialno,5,2),substr(put(datepart(agreementdate),date9.),1,2),amount) as recieptno,
amount, case when interest = . then 400
when interest > 700 then 300
else interest
end as interest
from test;
quit;
proc print;run;
HTH,
Rich
Dear Rich,
Thanks a lot for your .Kind information.
Regards,
Ashwini
thanks rich....this is a good idea to implement with cats and case statement.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.