In the datastep you are choosing one of two possible values for bdt (the last in sort order within the orders of acc and bid). To do the same in SQL in this instance, you can specify that the yield of your query include only the maximum value of bdt in each acc group. Since bid has the same value in each row of the original table, you do not have to do the same for bid in this instance.

You will also have to specify the DISTINCT qualifier to eliminate duplicate rows in test2. The acc groups have two rows each in the data.

This SQL query produces expected results:

proc sql; create table test2 as select distinct max(bdt) as bdt,acc,bid,sum(tsec) as tsec, sum(tcnt) as tcnt,sum(tfee) as tfee from test group by acc; quit;

Sig -----Original Message----- From: CKalisetty [mailto:ckalisetty@AOL.COM] Sent: Thursday, September 12, 2002 12:49 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: proc sql equivalent to data step

Hi May be it was confusing. Here is a simple form of that.

data test; input acc $ bid tsec tcnt tfee bdt; cards; 12 14 5 0 0 1 12 14 5 5 0 2 13 14 0 0 0 3 13 14 0 0 5 4 ; proc sort data=test; by acc bid bdt; run; data test1; set test; by acc bid bdt; if first.acc then do; t_sec=0; t_cnt=0; t_fee=0; end; t_sec+tsec; t_cnt+tcnt; t_fee+tfee; if last.acc; run; proc print data=test1; run; proc sql; create table test2 as select bdt,acc,bid,sum(tsec) as tsec, sum(tcnt) as tcnt,sum(tfee) as tfee from test group by acc,bid,bdt; quit;

proc print data=test2; run;

Obviously my proc sql is wrong. How can I code an equivalent proc sql for the above data step? Thanks for any help >Subject: proc sql equivalent to data step >From: ckalisetty@aol.com (CKalisetty) >Date: 9/12/2002 12:12 PM Eastern Daylight Time >Message-id: <20020912121243.10561.00004602@mb-cu.aol.com> > >Hello all >I am trying to write an proc sql equivalent to a data step. Here are both >pices >of codes. The problem is I am getting more obs. in proc sql. Am I doing some >thing wrong here. Any help is greatly appreciated. > >Thanks > >Sekhar > > >data u_bid14(drop=tsec t_cent a_fee usgcnt ckgsrcnt monthfee > commsur gamesur resubmit feeresub usgresub gamresub > surresub feeresu usgresu gamresu surresu); > set in1.cyv3200208. (rename=(accno=acct_num pricidx=price_in >cyclenum=cycle > totsec=tsec totcnt=t_cent annfee=a_fee >sessions=t_sess) > keep=accno cyclenum service_ busid PRICIDX YRMO_NUM >TOTCNT > totsec annfee usgcnt ckgsrcnt monthfee commsur >gamesur > resubmit feeresub usgresub gamresub surresub >bus_dt >CANDATE > CAN_CODE REACTDT access sessions); > by acct_num access bus_dt; > > retain tot_sec 0 tot_cent 0 annfee 0; > > if busid=14; > > if first.acct_num then do; > tot_sec=0; > tot_cent=0; > annfee=0; > sessions=0; > usgcn=0; > ckgsrcn=0; > monthfe=0; > commsu=0; > gamesu=0; > resubmi=0; > feeresu=0; > usgresu=0; > gamresu=0; > surresu=0; > end; > > tot_sec+tsec; > tot_cent+t_cent; > annfee+a_fee; > sessions+t_sess; > usgcn+usgcnt; > ckgsrcn+ckgsrcnt; > monthfe+monthfee; > commsu+commsur; > gamesu+gamesur; > resubmi+resubmit; > feeresu+feeresub; > usgresu+usgresub; > gamresu+gamresub; > surresu+surresub; > > if last.acct_num; > > access=1; > > > run; > >Here is the code I am trying to put in for above data step > >data u_bid14; > set in1.cyv3200208; > access=1; > run ; (how can i write this is sql); > >proc sql; > create table u_bid14 as > select sum(totsec) as tot_sec,sum(totcnt) as tot_cent,sum(annfee) as >annfee, > sum(sessions) as sessions,sum(usgcnt) as usgcn,sum(ckgsrcnt) as >ckgsrcn,sum(monthfee) as monthfee, > sum(commsur) as commsu,sum(gamesur) as gamesu,sum(resubmit)as >resubmi,sum(feeresub) as feeresu, > sum(usgresub) as usgresu,sum(gamresub) as gamresu,sum(surresub)as >surresu, > accno as acct_num,pricidx as price_in,cyclenum as >cycle,service_,busid,yrmo_num,bus_dt, > candate,can_code,reactdt,access > from u_bid14 > where busid=14 > group by acct_num,access,bus_dt; > quit; > > > > > > >