create blank column and concate 2 column in one row

[Ashwini]

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

[Vish33]

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

[rtritz]

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

[Ashwini]

Dear Rich,

Thanks a lot for your .Kind information.

Regards,

Ashwini

[Vish33]

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