You haven't shown the code that generates the report.  I assume it's inside a %stpbegin  block?   If you're using tagsets.excelxp the default is for titles and footnotes to go in the header/footer of the sheet.  Did you check there to see if they exist?   If you want to see the titles/footnotes in a cell in the spreadsheet, you can use the embedded_titles='yes' option. This is an option of tagsets.excelxp destination.   I think the way to do this would be to add to your excel block to create a global macro variable _odsoptions if upcase("&outtype") = "EXCEL" then do; rc = stpsrv_header('Content-type','application/vnd.ms-excel;'); rc = stpsrv_header('Content-disposition','attachment; filename=temp.xls'); call symput("odsdest","tagsets.excelxp"); call symput("_odsoptions", "embedded_titles='yes' embedded_footers='yes'"); *new line; end; This paper from the great Cynthia Zender explains the use of the global macro vars _odsoptions, _odsdstyle, etc: https://www.lexjansen.com/nesug/nesug07/ap/ap22.pdf   That said, tagsets.excelxp is pretty much deprecated, SAS introduced ODS EXCEL destination which will write .xlsx files 10 (?) years ago.  You might want to switch to ODS EXCEL.  But the general issue will likely be the same, you'll need to tinker with destination-specific options to get what you want. For comparison of tagsets.excelxp and excel destination, see this paper by the great Chevell Parker: https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf     hope that helps       ... View more

Thanks much @ShelleySessoms ! ... View more

Pick a subject value that you know is in both datasets, and check that PROC PRINT can find that value.  So if the subject ID is 333, then try: proc print data=AE.Log_AE; where subject='333'; run; proc print data=AE.Treatment; where subject='333'; run; Does that confirm that the subject exists in both datasets?   If so, please post the log from running: data want; merge AE.Log_AE (where=(subject='333') in=a) AE.Treatment (where=(subject='333') in=b) ; by subject; put (subject a b)(=) ; run; ... View more

Registration link: https://www.basug.org/event-details/basug-june-meeting-training-in-person ... View more

@Tom wrote in part: NOTE the %GLOBAL statement.  You cannot return macro variables from DOSUBL() into local macro variables. DOSUBL() can return side session global macro variables into main session local macro variables.  In this case, you can't use the %GLOBAL statement in the side session to create the macro variable, because it will throw (I think incorrectly): ERROR: Attempt to %GLOBAL a name (VARLIST) which exists in a local environment. But if you add an open-code %let statement inside the DOSUBL call, it will happily create a side session global macro variable, which will be returned to the main session local symbol table.   So code like: %macro list_vars_step(dataset,mvar=varlist); %if not %symexist(&mvar) %then %global &mvar; %let &mvar=; data _null_; if 0 then set &dataset; do while(1=1); length ___NAME___ $32 ; call vnext(___NAME___); if ___NAME___='___NAME___' then stop; if ' '=resolve('%let &mvar=&&&mvar '||___NAME___||';') then; end; stop; run; %mend list_vars_step; %macro list_vars(dataset); %local rc vars dsid i; %if %sysfunc(indexc(&dataset,())) %then %do; %local varlist; %let rc=%sysfunc(dosubl(%nrstr(%let varlist=;%list_vars_step(&dataset) %put INSIDE DOSUBL ; %put _user_ ; %put ; ))); /*added code here*/ %put AFTER DOSUBL ; %put _user_ ; %put ; /*added code here*/ %let vars=&varlist; %end; %else %do; %let dsid = %sysfunc(open(&dataset)); %do i=1 %to %sysfunc(attrn(&dsid, nvar)); %let vars=&vars %sysfunc(varname(&dsid,&i)); %end; %let dsid=%sysfunc(close(&dsid)); %end; &vars. %mend list_vars; %put %list_vars(sashelp.class(drop=sex)); Returns:   33 %mend list_vars; INSIDE DOSUBL GLOBAL VARLIST Name Age Height Weight LIST_VARS DATASET sashelp.class(drop=sex) LIST_VARS DSID LIST_VARS I LIST_VARS RC LIST_VARS VARLIST LIST_VARS VARS 34 35 %put %list_vars(sashelp.class(drop=sex)); AFTER DOSUBL LIST_VARS DATASET sashelp.class(drop=sex) LIST_VARS DSID LIST_VARS I LIST_VARS RC 0 LIST_VARS VARLIST Name Age Height Weight LIST_VARS VARS Name Age Height Weight The log is a bit out of order, but you can see that inside the DOSUBL block there is a global macro variable VARLIST,  the value of which is returned the local macro variable when DOSUBL completes.   ... View more

Today I would also use DOSUBL for this.  But before there was DOSUBL, I hit this problem.  I see it as a bug that that VARNAME function does not honor the KEEP/DROP option.  I've been told in that in SCL, it does honor KEEP/DROP.    Luckly, Søren Lassen posted some solutions to SAS-L.  Basic approach is to open the dataset twice, once with the keep/drop option, and once without.  With that, you can loop over the list of variables without the keep/drop applied, and use the VARNUM function to test if the variable exists in the dataset with the keep/drop applied.  Code like:   %macro ListVars(data); /*** Based on %VarList macro developed by Søren Lassen Posted to SAS-L December 2010 and May 4, 2015 Macro is complicated by fact that the varname function does not honor keep/drop option. Therefore need workaround to prevent er ror msg if user specifies keep/drop. The work around is to iterate over list of variables in dataset without keep/drop applied, and check if each variable is in the dataset with keep/drop applied. ***/ %local dsid /*id of &data, with any keep/drop options*/ dsid2 /*id of &data, without any keep/drop options*/ i var /*name of ith variable in &data*/ return rc ; %*If &data had a rename option, macro would not return the renamed variables; %*So user is not allowed to use rename option.; %if %sysfunc(find(&data,rename,i)) %then %do; %put ER%str()ROR: (%nrstr(%%)&sysmacroname) cannot include rename option on dataset.; %goto mexit; %end; %let dsid=%sysfunc(open(&data)); /*dataset with any keep/drop options*/ %let dsid2=%sysfunc(open(%scan(&data,1,%str(%( )))); /*dataset without keep/drop options*/ %do i=1 %to %sysfunc(attrn(&dsid2,nvars)); %let var=%sysfunc(varname(&dsid2,&i)); %if (%sysfunc(varnum(&dsid,&var))) %then %do; %if %superq(return) = %str() %then %let return=&var; %else %let return=&return &var; %end; %end; %let rc=%sysfunc(close(&dsid)); %let rc=%sysfunc(close(&dsid2)); &return %mexit: %mend ListVars; %put %listvars(sashelp.class) ; %put %listvars(sashelp.class(keep=_numeric_)) ; %put %listvars(sashelp.class(drop=_character_)) ; ... View more

See the approaches in this thread: https://communities.sas.com/t5/SAS-Programming/Print-the-same-SAS-Log-to-BOTH-the-log-window-and-external-file/m-p/966455/highlight/true#M376136   ... View more

Try the COMPBL function.  From the docs: "The COMPBL function removes multiple blanks in a character string by translating each occurrence of two or more consecutive blanks into a single blank." ... View more

I had an old sas ballot item (seems not to have been carried over to the SAS product suggestions) asking for the XLSX engine to support the DBSASTYPE option.  That would help with problems like this.   But if the goal is to make data fit a pre-determined template in order to fit into legacy processing, seems like instead of a dynamic %num_to_char, you might consider writing a macro that allows you to read a data dictionary (with variable types, lengths, position, etc) and then transforms a dataset to fit that dictionary. ... View more

If you remove the NOAUTOLEGEND option, does it give you something reasonable?   proc sgpanel data=plot noautolegend;by Replicate; ... View more

Do you know what the correct date or date-time value is, which this value represents?  If it is a millisecond timestamp, is it number of milliseconds since what zero point?   If it's number of milliseconds, you could convert it to number of seconds, but you need to know the zero point.  If the  0 is Jan 1, 1960, then it would represent 24Apr2015 at 4:00am:   18 data a ; 19 x=1.7454672E12 ; 20 x=x/1000 ; 21 format x datetime19. ; 22 put x= ; 23 run ; x=24APR2015:04:00:00 NOTE: The data set WORK.A has 1 observations and 1 variables.       ... View more

Apologies @Kurt_Bremser .  When I moved this to visual analytics, I accidentally moved your reply first, so you're now the owner of the thread, instead of @P3 who asked the question.  I don't see a way to make the original question the question again.   ... View more

It looks like NOW() is a Visual Analytics function, not a SAS language function.  I'm going to try moving this question the visual analytics community. ... View more

I think it's important to define the scope of the reset.  To me, "resetting SAS" implies a fresh SAS session (empty work library, no user-created macro variables, no user librefs assigned, etc etc.) .     Are you really only interested in clearing the log window and results window? ... View more