I took out the single quotes on my macro variables and placed double quotes (or single quotes) around the variable when called but the PROC SQL doesn't like it. I'd like to take the warnings people have given me about not putting quotes in the macro values if there is a quick fix otherwise i have to go back to placing single quotes in the macro variable values when using them in PROC SQL. It might have something to do with placing the code within EXECUTE blocks of the proc sql.   Note: the 11/1/2015 in the error message below is referencing another variable named &startdate that was closer to the top of code.   %let enddate=11/1/2016 ;   10853 WHERE StandardDrugType = 'chemotherapy' 10854 AND AdministrationDate < "&enddate." 10855 AND YEAR(DiagStartTherapyDate) - PatientMasterBirthYear >= 18 10856 ) by myconn ; ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '11/1/2015'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '11/1/2016'.   The code works when %LET enddate='11/1/2016' ; ... View more

thank you! ... View more

Tried that for other variables and still having trouble which is why I am switching to macros. This one doesn't like the &year_start1 which was created from the first symput. Yearlotend is a data variable that holds the years 2012, 2013, 2014, 2015, 2016 from a dynamic table produced from a proc freq out= option.  The future PROC SQL looks at the last day of the prior year (12/31/{2 digit prior year} which is why I need to assign a global variable. It doesn't necessarily have to be a two-digit year but it doesn't work even with the full year (yearlotend) entered into the first call symput. If you have any solutions that might help me understand this better would be helpful because if it will be easier than maintaining macros that would be great!   data _NULL_; set yearplan ; yearlotend2=input(substr(put(yearlotend,4.),3,2),2.)-1 ; suffix=put(_n_, 5.) ; call symput(cats('year_start',suffix),yearlotend2); call symput('projstart', "'"||catx("/","12/31",&year_start1.)||"'") ; run; %put &projstart; ... View more

also, when I tried creating the variables in a data _NULL_ , it was not understanding the run; statement because nothing other than macro variables were being processed (I think) ... View more

Thanks. I have several long PROC SQLs that use the macro variables so I have to create them outside the datastep. I do additional processing in datasteps but have to pull from SQL first.  ... View more

thank you! I posted another question in the latest response to my initial problem so if you have time to take a look that would be great. ... View more

Thank you. I have made changes to my code and removed quotes for most except one that is a special case. I thought you might be able to help with conditional macro variables. I tried to use macro facility to do this but it is not working. Also since I had to use the %str() for one of my global variables I am not sure how to reference it in the macro - at least it doesn't seem to be catching the CRC if-then statement. Do you see what might be causing the oral1 and oral2 macro variables from not being created?   %LET Diag=%str(CRC) ; /*for file names: CRC, MM, NSCLC, Unknown*/ LIBNAME dataPrep "C:\data\&diag.\" ; %LET enddate = 11/1/2016 ;   /*for validation*/ %LET adminyr = %scan(%trim(&enddate.),3); %LET adminmth = %scan(%trim(&enddate.),1);   /*orals*/ %macro orals() ; %if &diag.='MM' %then %do ; %LET oral1=lenalidomide ; %LET oral2=melphalan ; %end; %else %if &diag.='CRC' %then %do; %LET oral1=capecitabine ; %LET oral2=regorafenib ; %end ; %mend ; %orals; %put &oral1 &oral2 ; ... View more

any reason why I would get a mismatch data type error for the _date field when converting code to my real drug dataset? My drug data is in date9. format (16NOV2016) for a field named administrationdate. I changed all the 'date' to 'administrationdate' but left the _date field the same.  ... View more

yes! that works. Sorry , i must have copied the old code. Thank you !! ... View more

thanks Mark. it is  accessing a hash interator and still fails. I think I tried using next() months ago and gave up on it.    if 0 then _color=color; format _date date9.; call missing(_date); dcl hash h1_colors(multidata:'n', ordered:'y', hashexp:4); dcl hiter hh1_colors('h1_colors'); h1_colors.defineKey('id','color'); h1_colors.defineData('_color','_date'); h1_colors.defineDone();   ... View more

Thanks! My system doesn't seem to like the next() iterator and produces an error. I have a question to sas support about version issues for this so I'll wait and see what they say. My 'have' dataset is 450 records but only 224 were read from it before the error. I checked row 224 where it got cut off and I don't see any odd data. So, I really appreciate your help and will look again at it after I hear back from support.    307 /* populate combo */ 308 if last.date then 309 do; 310 _rc = hh1_colors.first(); 311 do while (_rc = 0); 312 if intck('day',_date,date)<=input(color,color_interval.) then 312! combo=catx('-',combo,_color); 313 else h1_colors.delete(); 314 _rc = hh1_colors.next(); 315 end; 316 end; 317 318 if first.date and last.date then 319 /* only 1 color for the date */ 320 do; 321 output; 322 end; 323 else if last.date then 324 do; 325 /* multiple colors for the date */ 326 _rc = hh1_rowsSameDate.first(); 327 do while (_rc = 0); 328 output; 329 _rc = hh1_rowsSameDate.next(); 330 end; 331 end; 332 333 run; NOTE: There were 0 observations read from the data set WORK.HAVE. ERROR: Invalid hash object for iterator operation at line 314 column 15. ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 226 observations read from the data set WORK.HAVE. WARNING: The data set WORK.WANT may be incomplete. When this step was stopped there were 224 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.07 seconds cpu time 0.07 seconds   ... View more

Thanks for trying! I can run the code without errors this time but when I use my sample dataset with 48 rows, the 'want' table only ends up with 13 records. So it's not giving me the right combinations for each row.  Not sure it's even doable but thanks for trying.  ... View more

also, I don't want to drop the color/drug altogether if it shows up later on or with another combination. I just want to drop the color/drug from the combination at day 30 (or 45) if it hasn't been added. If it gets added at day 32 (or 47) then it starts either its own combination or merges with another combination if other colors/drugs are entered on the same day.    Thanks for you help.  ... View more