BookmarkSubscribeRSS Feed
Ashwini
Calcite | Level 5

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

4 REPLIES 4
Vish33
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Calcite | Level 5

Dear Rich,

Thanks a lot for your .Kind information.

Regards,

Ashwini

Vish33
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2694 views
  • 0 likes
  • 3 in conversation