This sql query seems to give me the desired output, but it also note below ("one or more Cartesian product joins that can not be optimized") makes me wonder if something is amiss.   Is there a fix to the code that will not generate the note?   27 /*Assign Drug Categories where possible to Ingredients not 28 associated with a drug category*/ 29 PROC SQL; 30 Create Table FDA_RESOLVED_Process as 31 SELECT FDA.Active_Ingredient 32 ,FDA.Initial_Posting_Date 33 ,FDA.Resolved_Date 34 /*if there is a match, put drug_category, if not use "Not on List"*/ 35 , coalescec(EML.Drug_Category, 'Not in List') as Drug_Category 36 /*Ingredient list*/ 37 FROM Fda_resolved_data_WO_Blank as FDA 38 /*Drug Category and Name list*/ 39 LEFT JOIN Essential_medicines_list as EML 40 /*select if drug name from Essential Medicines is in Active Ingredient*/ 41 ON findw(FDA.Active_Ingredient, trim(EML.DRUG_NAME_)) 42 ; NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. NOTE: Table WORK.FDA_RESOLVED_PROCESS created, with 129 rows and 4 columns. 43 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds ... View more

I'm trying to select all obs that have a particular value across a multiple variables in PROC SQL.   I know it can be done with a do loop in a data step, but in this case I'm using proc sql because I'm trying to avoid doing a data step and then a proc print.   The sample code below works, but I'm wondering if there is a more efficient way.   /*create data set*/ data a; input x 1. y 1. z 1.; datalines; 000 100 213 ; run; /*list if any variable has a value of 1*/ proc sql; select * from a where x=1 or y=1 or z=1 ; quit; ... View more

Not sure why this isn't working, shouldn't I have "end" in the location of the error?   28 proc sql; 29 create table temp as 30 select contract_number, sum(enrollment) as parent_enrol, 31 case when (calculated parent_enrol lt 50000) then 1 32 else case when parent_enrol le 200000 then 2 33 else case when (calculated parent_enrol gt 200000) then 3 34 else 4 35 end as parent_size __ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored. 36 from D107342.AP15747022_Master 37 group by contract_number; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 38 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time ... View more

Can I assign a macro value with a format.   I'm able to do it in a data step, but similar code doesn't seem to work with a macro value.   27 /*value is correctly assigned in data step*/ 28 data _null_; 29 y=put('01',$plan_type.); 30 put y; 31 run; HMO NOTE: DATA statement used (Total process time): real time 0.15 seconds cpu time 0.00 seconds 31 ! 32 33 /*Doesn't work for macro value*/ 34 %let grp=%sysfunc(putc('01',$plan_type.)); 35 %put &grp; '01' ... View more

I want to rename the same three variables across a group of files.   Thought %str would work, but it didn't   27 %let ren=%str(pbp_a_hnumber=H_Number pbp_a_plan_identifier=Plan_ID segment_id=Segment_ID);  28 27 %let ren=%str(pbp_a_hnumber=H_Number pbp_a_plan_identifier=Plan_ID segment_id=Segment_ID);  _ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 29 Data pbp_b1a; 30 set D107342.pbp_b1a (keep=&keepvars pbp_b1a_auth_yn ___ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 31 rename=( 32 %bquote(&ren) 33 pbp_b1a_auth_yn=inpatient_acute_auth 34 )); 35 run; 36 ... View more

I'm running a bunch of programs with pre-written code that includes infile statements with URLs such as the code below.   infile '.\pbp\pbp_b1a_inpat_hosp.txt' delimiter = '09'x dsd lrecl = 30000 firstobs=2 missover;   Of course, I can manually change the "." to a directory for each program, but I'm wondering if there is a way "point" the period to a directory. ... View more

macro generated code in the log from using the MPRINT option often appears as one long stream of continuous text.   Is there a way to retain the indentation and hard returns from the original program code? ... View more

I'm aware that INTCK counts the number of intervals between two periods, but I'd like to get a truncated version of the actual count (i.e. 12 rather than 12.3 or 12.7).   I think this code does it but I'm wonder if there is function that would do the same rather than multiple lines of code   data _null_; format s_dt l_dt alt_end date9.; s_dt = mdy(12,31,2008); l_dt = mdy(1,1,2010); months = intck('month',s_dt,l_dt); alt_end = intnx('month',s_dt,months,'same'); if l_dt lt alt_end then trunc_month=months-1; else trunc_month=months; putlog _all_; run;         ... View more

I'm trying to store a format name in a macro variable.   How do I invoke a macro value with a "$"?   ... View more