proc sql; select int(count(*) * .05) as recs into :Recs from have; quit; proc sql outobs=&recs; create table offers as select * from have order by total_trans_amt desc; quit; ... View more

The only thing your doing is making a copy of temp1 with a subset of the columns? ... View more

Can't you get there from the first table? proc sql; create table table0_ as select      alcohol,      sum(case when treatment='drug1' then numbers else 0 end) as drug1,      sum(case when treatment='drug2' then numbers else 0 end) as drug2,      sum(case when treatment='drug3' then numbers else 0 end) as drug3,      sum(case when treatment='drug4' then numbers else 0 end) as drug4 from      table0 group by alcohol; /*if na is not a valid value for alcohol, then you can insert the record*/ insert into table0_ (alcohol,drug1, drug2, drug3, drug4) values('NA',0,0,0,0); quit; ... View more

I don't quite understand your desired logic...but you can join the table to itself with appropriate date criteria to get all of the values on one row...then do the math. proc sql; create table want as select      t1.prod,      t1.cust,      t1.req_ord_qty,      t2.req_ord_qty as req_ord_qty_2,      t3.req_ord_qty as req_ord_qtr_3 . . . from      have t1      left join have t2           on t1.prod=t2.prod                and t1.cust=t2.cust                and intnx('month',t1.per_dt,-1,'begin')=t2.per_dt      left join have t3           on t1.prod=t3.prod                and t1.cust=t3.cust                and intnx('month',t1.per_dt,-2,'begin')=t3.per_dt      left join have t4           on t1.prod=t4.prod                and t1.cust=t4.cust                and intnx('month',t1.per_dt,-3,'begin')=t4.per_dt      left join have t5           on t1.prod=t5.prod                and t1.cust=t5.cust                and intnx('month',t1.per_dt,-4,'begin')=t5.per_dt      left join have t6           on t1.prod=t6.prod                and t1.cust=t6.cust                and intnx('month',t1.per_dt,-5,'begin')=t6.per_dt      left join have t7           on t1.prod=t7.prod                and t1.cust=t7.cust                and intnx('month',t1.per_dt,-6,'begin')=t7.per_dt ; quit; ... View more

data a; newp=.00005; do gen=1 to 10; seed=47639087; expon=2*ranuni(seed) + 4; n=round(10**expon,1); p=.newp; q=1-p; NumRR = round(n*p*p,1); NumSR = round(2*n*p*q,1); NumSS = round(n*q*q,1); newp=(NumRR + .5*NumSR) / (Numrr + numsr  + numss); diffp=p-newp; newq=(NumSS + .5*NumSR)/ (Numrr + numsr  + numss); diffq=q-newq; output; end; ... View more

You could also do something like this: create table work.have as select * from connection to ODBC (      select * from.... or really any database specific statement could go here ); ... View more

I think your environment is not configured correctly.  Here are some links to help: http://support.sas.com/techsup/technote/ts765.pdf SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition ... View more

Simplistic approach would be to subtract enroll_start from enroll_end and add 1 for the end date (presuming the values are actual dates)... As an aside, I was confused by the value sum_enroll=4 for the second row.  Start day1 and end day7 means 7 days, right? ... View more

keyword is wrong.  should be sqlsvr presuming you've purchased the sas connector to sql server.  If not, you'll probably need to use the odbc connector if you're running locally. proc sql noprint; connect to sqlsvr as CTWorks (datasrc="XXXX" user = "XXX" password = "XXX" insert_sql = yes insertbuff = 10000); disconnect from CTWorks; quit; ... View more

Does this not get what you need: %Let dtbegin = '2013-08-01' ; %Let fpath = 'C:\imports\' ; data work.test; dt2=intnx('month',input( &dtbegin, yymmdd10.),1) ; call symput('fname',cat('',&dtbegin,' report name',put(dt2,monname10.),' ',put(year(dt2),4.),'.xlsx')); run; %put fname=&fname; fname= 2013-08-01 report name September 2013.xlsx ... View more

When you have criteria applied to the right side of a left join it essentially becomes an inner join.  What indexes are available on the oracle tables? ... View more

If you have data like this: ID      Dt     Delq 1     31Jan2013     0 1     28Feb2013     0 1     31mar2013     0 1     30Apr2013     1 2     31Jan2013     0 2     28Feb2013     0 2     31Mar2013     0 2     30Apr2013     0 Then you could do this: proc sql; create table no_delq_in_3_months as select      t.* from      have t      left outer join have t1           on t.id=t1.id and intnx('month',t.dt,-1,'end')=t1.dt and t1.delq>0      left outer join have t2           on t.id=t2.id and intnx('month',t.dt,-2,'end')=t2.dt and t2.delq>0 where      t1.dt>='31Mar2013'd      and t1.delq=0      and t2.id is null      and t3.id is null ; quit; Depending on size, the datasets might need sorting or indexing. db ... View more