how about if s1 * s2 * s3 * s4 * s5 = 0 then set='M'; else sex='F'; ... View more

Others have given data step suggestions...you could also use proc sql (especially if the data are in a rdbms somewhere else)... proc sql; select * from have where TINT='2' and ( NAME like '% TRUST %' or name like  '% TR %'); quit; ... View more

You're only selecting three variables to group by but your grouping by 4.  Not sure if that's by design. One other issue with oracle is that I believe SAS considers most oracle date columns (at least all of ours) to be datetime, not date...so you would have to use '01Jan2013:0:0'dt. ... View more

I've done it like this: select DISTINCT catx("'",'TABLE'n ,"'") into :varlist separated by ', ' from USERSPDS.TABLE1; quit; %let varlist = &varlist; Proc sql; select DISTINCT catx("'",'LIBREF'n,"'") into :var2list separated by ', ' from USERSPDS.TABLE1; quit; %let var2list = &var2list; where n1.name IN (&varlist) and n1.libname IN (&var2list); ... View more

I'm trying to take advantage of the new syntax for creating a connection using an already existing library.  Often, oracle queries need tuning to achieve optimal performance which is done through query hints.  SAS thinks these are comments and does not pass them through unless the PRESERVE_COMMENTS option is enabled on the connection.  Using the new syntax doesn't seem to allow that but it does allow end users to make a direct connection much more easily. For example, if I have an library defined for an oracle database (MDM), I can certainly use implicit pass through.  I can't set the PRESERVE_COMMENTS option but it doesn't matter since SAS is generating the actual oracle query. If I define a connection directly, I can set the option which allows me to do something like connect to oracle as cmdm (path=...user=...pw=....PRESERVE_COMMENTS) create table work.tmp as select * from connection to cmdm (select /*+parallel(6)*/ from.....); without the PRESERVE_COMMENTS option, the parallelization hint is not sent to oracle. With the new syntax, all you need to make a separate connection is connect using mdm as cmdm; but I can't find a way to enable the preserve_comments option. ... View more

how about something like this? proc sql; create table want as select      patient_id,      medication_name,      min(Service_Date) as Medication_Use_Index_Date from      have t1 where exists (select * from want where patient_id=t1.patient_id and medication_name=t1.medication_name and service_date > t1.service_date and intck('month',t1.service_date,service_date)<=6); quit; ... View more