Hi, everyone.    I'm trying to merge a smaller dataset into a larger dataset with repetition of the values of a specific variable from the small dataset. I want to take the smallclient variable from the first table below and add that variable with repetition to the second table below for each combination of au and fyear. I tried to code this with the following, but it failed. How would I code this?   *merge with repeated values - first example failed testgroup2 is the larger dataset; data Practice.merged_testgroup; merge Practice.testgroup2(in=a) Practice.testgroup5(in=b) / repeated; by au fyear; run;       ... View more

I’ve noticed that when enabling the MFILE option, which is very handy in many situations ranging from debugging to producing production-ready programs, program execution time is severely impacted. Much more than the simple I/O operation of writing the MPRINT statements already being written to the program log would suggest. Depending on the complexity and amount of macro code to be resolved, this may cause the execution to take 5-7 times longer. And to be precise, I am only referring to adding the MFILE option on top of the MPRINT. I’ve coded a simple example in which the performance within SODA is ~2.5 times slower. I’ve noticed this behavior in the past, in local DM, SAS server, SAS EG, etc. applications., in Windows environments etc. It seems like a global issue with the MFILE implementation.   To investigate, I’ve also played around with the FILENAME statement (in which the MPRINT fileref is set) but it seems to have little impact.   Here is a snip of the proc print and means output   @Sas technical support? Anyone has any ideas?   The code used to test: %macro test; %do i=1 %to 250; data t&i.; %do j=1 %to 100; do k&j.=1 to 5; output; end; %end; run; %end; %do i=1 %to 250; data t&i.; %do j=1 %to 100; do k&j.=1 to 5; output; end; %end; run; %end; %mend; %macro wmfile; filename mprint temp; options mprint mfile; %test; options nomfile; %mend; %macro womfile; options mprint nomfile; %test; %mend; data bench; length onbench $200; do run=1 to 3; do onbench='%wmfile', '%womfile'; start=time(); rc=dosubl(onbench); end=time(); mark=end-start; output; end; end; run; proc print data=bench; run; proc means data=bench; class onbench; var mark; run; 20 year SAS user, but first post here. Thanks for your time. ... View more

I am attempting to output an excel (xlsx) file using ods excel from a proc output. My variables are in date9. format in the SAS data set, but when they output to excel they are in a text format.    This is my code:    ods excel file="\\xxxmyfilelocation\Events_%sysfunc(date(),yymmddd10.).xlsx" style=forNESUG options (FLOW='Tables'); ods excel options (zoom='80' sheet_name='Tracker'); proc report data=tracker nowd split= "_" style(column)=[JUST=LEFT VJUST=TOP FONT=(Calibri, 11pt, NORMAL) BORDERSTYLE=SOLID BORDERCOLOR=black] style(header)=[BACKGROUNDCOLOR=light grey FONT=(Calibri, 11pt, BOLD) JUST=CENTER VJUST=TOP  BORDERSTYLE=SOLID BORDERCOLOR=black]; column Initial_Code_Date Initial_Attribution_Date Initial_SO_Coverage Date_of_AE_status_Evaluated Initial_Rep_event_became_SAE Type_of_SAE Final_attribution Study_RX_held Treatment_rechallenged Treatment_Permanently_Discontinu Status_AE  SO_FU_Log_Comments AF_summary_comments Final_MedDRA_code Final_MedDRA_LLT FINAL_Code_Date Final_Attribution_Date FU_SO_Coverage Initial_site_email_archived_  Final_site_email_archived_ Completed_pharma_reporting_ Closed SO_closed_AE Close_Date Teaching_example Tracker_run_date;   define Initial_Code_Date/'Initial Code Date' style={background=lightyellow color=black};   define Initial_Attribution_Date/'Initial Attribution Date' style={background=lightyellow color=black};  define FINAL_Code_Date/'Final Code Date'  style={background=lightyellow color=black};  define Final_Attribution_Date/'Final Attribution Date' style={background=lightyellow color=black};   define SO_Log_Comments/'SO Log Comments' style(column) = [just=left cellwidth=3in background=lightyellow color=black] style(header)=Header{background=lightyellow color=black};   define SO_FU_Log_Comments/'SO FU Log Comments' style(column) = [just=left cellwidth=3in background=lightyellow color=black] style(header)=Header{background=lightyellow color=black};    define Reas_NonReportable_AE/'Reas NonReportable AE' style(column) = [just=left cellwidth=0.5in background=lightyellow color=black] style(header)=Header{background=lightyellow color=black}; define Teaching_example/'Teaching example' style={background=lightyellow color=black}; define Closed/'Closed' style={background=lightyellow color=black};  define SO_closed_AE/'SO closed AE' style={background=lightyellow color=black};  define Close_Date/style={background=lightyellow color=black};  options missing=" "; format Initial_Code_Date date11. Initial_Attribution_Date date11. Final_Code_Date date11. Final_Attribution_Date date11. Close_Date date11.; run;      I think the issue is my define statement, because the last block of code which exports to a different sheet in the excel workbook exports the date correctly and there is no define statement.    proc report data=sites nowd split= "_" style(column)=[BACKGROUNDCOLOR=white JUST=LEFT VJUST=TOP FONT=(Calibri, 11pt, NORMAL) BORDERSTYLE=SOLID BORDERCOLOR=black] style(header)=[BACKGROUNDCOLOR=light grey FONT=(Calibri, 11pt, BOLD) JUST=CENTER VJUST=TOP BORDERSTYLE=SOLID BORDERCOLOR=black]; column SiteNo Name Status Country Status_Date Note; format Status_Date date11.; run;   Any suggestions?  ... View more