If it wasn't because the options did not run then it was because you told it NOT to make a new sheet.  ods excel file="c:\downloads\embedded_titles.xlsx" options(sheet_interval="none" embedded_titles="yes" embedded_footnotes="yes" flow="tables") ; ods excel options(sheet_name="MALES"); proc print data=sashelp.class; where sex='M'; Title "Males Only"; run; ods excel options(sheet_name="FEMALES"); proc print data=sashelp.class; where sex='F'; Title "Females Only"; run; ods excel close;   When you change the sheet name you need also tell it to start a new sheet.  So either set the SHEET_INTERVAL option to "PROC" or tell it start and new page when you change the name. ... View more

As others have pointed out, you never specified what rules you want to execute.  So you have left the job of interpreting the objective to us.  Some folks (including me) have, after some study, deduced that each entry in the new variable is supposed to be extracted from successive parenthetical expressions.      At least that is what seems to be the case, even though your sample result does not completely honor that rule.  That's why respondents have posted the question about row 2, and about MEASLES vs Rubella in line 1.   To help us help you, please be complete in describing the task.   Here is my suggestion, based on capturing consecutive pairs of parenthetical expressions - I call the first expresssion _CONDITION, and the second as _CODES.  Then it checks _CODES for "IgM" and "IgG" to decide whether to add content to the new variable:   data Test; length Test_Name $2000; infile datalines delimiter='#'; input Test_Name; datalines; Encephalitis Antibody Panel, CSF Includes: Herpes Simplex Virus (HSV) 1/2 (IgG) Type Antibody, CSF Lymphocytic Choriomeningitis (LCM) Virus (IgG, IgM) Antibody, IFA, CSF Measles (Rubeola) (IgG, IgM) Antibody Panel, IFA, CSF Mumps Antibody Panel, IFA, CSF Varicella-Zoster Virus (VZV) Antibodies (Total, IgM), ACIF/IFA, CSF West Nile Virus (WNV) Antibodies (IgG, IgM), CSF # Herpes Simplex Type 1 and Type 2 Glycoprotein G-Specific Antibodies, IgG by CIA# run; data want (drop=_: L R); /*L & R for left & right parentheses*/ set test; length Sub_Analyte $1000; do L=findc(test_name,'(') by 0 while(L^=0); R=findc(test_name,')',,L); if R=0 then leave; _condition=substr(test_name,L+1,R-L-1); L=findc(test_name,'(',,R); R=findc(test_name,')',,L); if L=0 or R=0 then leave; _codes=substr(test_name,L+1,R-L-1); do _txt='IgG','IgM'; if findw(_codes,trim(_txt),', ')>0 then sub_analyte=catx(', ',sub_analyte,cats(_condition,'_',_txt)); end; L=findc(test_name,'(',,R); end; put sub_analyte=; run;         ... View more

@ybz12003 wrote: My Order_Test variable is a character, not numeric. Ok. So?  What does that mean in terms of what type of report you want?   Your original post did not really explain what you wanted.  You might be able to make it clearer by sharing some example data and what report you want from that data.   ... View more

You must learn to read (Maxim 2) and analyze your log. NOTE: Variable end is uninitialized. This tells you that you used the keyword in a place where SAS thinks it is a variable name. Let's go looking for this. This is it: if end=last then do; Syntactically, you compare the variables end and last. But there is no variable end defined or set anywhere. But there is this: set work.CoV_251_NoUpdated_CNT end=last; where you define the variable last as boolean (numeric, contains only 0 or 1), which is set to 1 when the DATA step reads the last observation of the dataset. What you most probably wanted is this: if last then do; ... View more

For your example you could also check for changes in DATE.  But perhaps your general case is to look for changes in either. proc sql; create table want as select * , case when (count(distinct date)>1) then 'Y' when (count(distinct sampleid)>1) then 'Y' else 'N' end as Change from test group by state,caseid ; quit; ... View more

Why do you have two ampersands?     If the name of the macro variable is Panel_ID_list then you only want one. If the name is being built from the value a macro variable named PANEL then you need three ampersands. &&&Panel._ID_list On the first pass the macro process will convert && to & and remind itself to make a second pass.  On the first pass it will convert &panel. into the value of PANEL so that when it gets to the second pass the name of the macro variable will be COV_HA_ID_LIST.   PS You usually do NOT want to use STRIP() the way you have when moving character variable values into macro variables.  If the actual value has leading spaces then STRIP() will remove them and then the resulting quotes string will no longer match the actual value.  Just use TRIM() instead.  You also can make the macro variable easier to work with by using space instead of comma as the delimiter.  SAS code does not care which you use (or if you combine them). select quote(trim(ID)) into : CoV_HA_ID_list separated by ' ' from CoV_HA_IDs ; That way you could just define the macro to expect the actual list of values. %macro aaa (id_list); proc tabulate data=NREVSS251_vw_&st.; class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2; table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N); where eor_spec_id in (&id_list); run; %mend aaa; %aaa(&CoV_HA_ID_LIST); ... View more

If you want to use a DATETIME value show a date type appearance SAS did not provide then roll your own format. Proc Format has a bunch of directives to use with the PICTURE statement to show just about any element of a date, time or datetime value you may reasonably want.   This example creates a DT_mmddyy format analogous to the SAS supplied DTDATE format. proc format library=work; picture dt_mmddyy (default=10) low-high = '%0m/%0d/%Y' (datatype=datetime); run; data example; x=dhms(today(),0,0,time()); format x dt_mmddyy.; run; Note that picture statements are one place that you pretty much need to use the single quotes otherwise the directives will be treated as macro references and cause errors. You did not specify that the month and day should be preceded by zero when single digits. To closer appear like MMDDYY10 I used the 0s. The / and other characters would be literals. So if you want 10-12-2023 (dashes between bits) use the - character in the directive list. Or a space, comma, decimal or drive people nuts with ^ or < as separators. ... View more

Yes. ... View more

The Collection_Date volume (mmddyy10.) is in the "Have" dataset, but not in the CNT frequency output dataset. ... View more

Thank you for the suggestion; that is an awesome program. ... View more

I strongly suggest providing an example data set and then show us which of the percentages that will appear in  Lab_id that you are asking about. Here is an example of creating a data set with the output percentages that you should be able to run to see what I mean: proc freq data=sashelp.cars ; table origin*make*type/out=work.percent outpct ; run;   Then it may be time to discuss how you intend to use that list of variables. it may be better to generate a data set(hint: likely needed for the macro approach) and use the values in the data set. ... View more

Try this:   proc sql; create table Code_unMatch as select DISTINCT a.code,b.OBR_Code from Master_Code as a full join code_check as b on a.code=b.OBR_Code order by a.code; quit; You should find the unmatching text this way. ... View more

What type (numeric or character) of variable is weekcode in ND_Preval?  Does it have a format attached? What format?   If it is numeric variable with a date type format (DATE, YYMMDD, etc) attached then your macro variables should have normal date values because of the use of MIN() and MAX() operators will remove the formatting.  So your code should work.   1 data test; 2 today=date(); 3 format today date9.; 4 run; NOTE: The data set WORK.TEST has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 5 proc sql noprint; 6 select min(today) into :today 7 from test; 8 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 9 %put &=today; TODAY= 23490 But if it is character then the whole premise of your code can only work if the variable has strings like, 2024-04-24, that will sort in the same order as the date values that humans would see them as having.  Because when represented as string "01JUL2023" comes before "12SEP2000" because "0" comes before "1".     But if the character variable does have strings in that YYYY-MM-DD (or YYYYMMDD) style then the macro variables will also and so your where condition will look something like:   2023-01-01 le WeekCode le 2024-12-31 Which SAS will interpret as comparing the numbers 2021 and 1981 to a character string, hence the type mismatch.     ... View more

That works! ... View more