topic Re: create blank column and concate 2 column in one row in SAS Programming

create blank column and concate 2 column in one row

Ashwini — Wed, 28 Mar 2012 17:35:18 GMT

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

Re: create blank column and concate 2 column in one row

Vish33 — Wed, 28 Mar 2012 19:06:32 GMT

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

create blank column and concate 2 column in one row

rtritz — Wed, 28 Mar 2012 21:43:42 GMT

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

create blank column and concate 2 column in one row

Ashwini — Thu, 29 Mar 2012 01:14:02 GMT

Dear Rich,

Thanks a lot for your .Kind information.

Regards,

Ashwini

create blank column and concate 2 column in one row

Vish33 — Thu, 29 Mar 2012 06:59:22 GMT

thanks rich....this is a good idea to implement with cats and case statement.