topic Re: Export to Excel - want excel file with multiple sheets according to variable name in SAS Programming

Export to Excel - want excel file with multiple sheets according to variable name

ark123 — Fri, 19 Jan 2024 16:49:22 GMT

I have a file with information on patients which includes the name of the facility where they sought care. I am trying to export this file to excel in a way that each facility is exported to an individual sheet. I also have statements to specify the formatting of the file that I want to keep. I've included my SAS code below. This exact SAS code works fine for another file I have, but it is not working on this particular file (although it has worked previously). I cannot figure out why that is. I made sure my facility names comply with Excel's sheet name rules and no records are missing a facility name.

Do you have any suggestions?

ods excel file = "filepath\filename.xlsx";		
ods excel options (sheet_interval="bygroup" sheet_name="#byval(facility)");

proc sort data = filename;
	by facility Duplicate;
run;

options nobyline;
proc report data = filename STYLE(Header)={foreground=black background=WHITE};
	by facility;
	columns ('Monthly Report' patient Last_Name First_Name DOB Quality) facility ID	Contact Result notes Duplicate;
	define patient / 'Patient Number';  
	define Last_Name / 'Last Name' ;  
	define First_Name / 'First Name' ;
	define DOB / 'Patient DOB' ;
	define Quality / 'Quality Assurance';
	compute Quality;
		if Quality in ('Late' 'Early') then call define (_row_,"style","style={background=#a6bddb}");
		if Quality = 'Missed' then call define (_row_,"style","style={background=#fff7bc}"); 
	endcomp;
	compute Duplicate;
		if Duplicate = 'yes' then call define (_row_,"style","style={background=#7fcdbb}"); 
	endcomp;
run;
options byline;
ods excel close;

Thank you!

Re: Export to Excel - want excel file with multiple sheets according to variable name

PaigeMiller — Fri, 19 Jan 2024 18:44:30 GMT

This exact SAS code works fine for another file I have, but it is not working on this particular file (although it has worked previously).

In order to help you fix something, we need to know what is wrong. "Not working" really isn't enough information to help us understand. What is it doing that indicates to you "not working"? Please be specific and detailed. If possible, show us screen captures (and please use the "Insert Photos" icon to include your screen capture in your reply, do not attach files).

It probably would also help if you showed us the LOG for this code, by copying the ENTIRE log (not just errors or warnings but every single line) and pasting it into the window that appears when you click on the </> icon here.

Re: Export to Excel - want excel file with multiple sheets according to variable name

A_Kh — Fri, 19 Jan 2024 18:34:32 GMT

You better check your log and post it here.
The code itself looks just fine and it works when tested using sashelp.cars dataset.

ods excel file = "&path\cars.xlsx";		
ods excel options (sheet_interval="bygroup" sheet_name="#byval(origin) cars");

proc sort data=sashelp.cars out=cars; by origin; run; 
options nobyline;
proc report data = cars STYLE(Header)={foreground=black background=WHITE};
	by origin;
	columns origin make model type invoice; 
	define make / 'Make';  
	define model / 'Model' ;  
	define type / 'Type';
	compute type;
		if type eq 'SUV' then call define (_row_,"style","style={background=#a6bddb}");
		if type in ('Truck' 'Wagon') then call define (_row_,"style","style={background=#fff7bc}");
	endcomp;
run;
options byline;
ods excel close;

log.

135  proc sort data=sashelp.cars out=cars; by origin; run;

NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set WORK.CARS has 428 observations and 15 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


136  options nobyline;
137  proc report data = cars STYLE(Header)={foreground=black background=WHITE};
138      by origin;
139      columns origin make model type invoice;
140      define make / 'Make';
141      define model / 'Model' ;
142      define type / 'Type';
143      compute type;
144          if type eq 'SUV' then call define (_row_,"style","style={background=#a6bddb}");
145          if type in ('Truck' 'Wagon') then call define
145! (_row_,"style","style={background=#fff7bc}");
146      endcomp;
147  run;

NOTE: There were 428 observations read from the data set WORK.CARS.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.43 seconds
      cpu time            0.36 seconds


148  options byline;
149  ods excel close;
NOTE: Writing EXCEL file: C:\Users\Desktop\test\cars.xlsx

Output.

Re: Export to Excel - want excel file with multiple sheets according to variable name

ark123 — Fri, 19 Jan 2024 22:03:41 GMT

Here is a copy of the log:

I was able to get the file to export, but it exports into one sheet rather than individual sheets labeled by the facility name (see below). I would like for each facility to be put into a separate sheet labeled by that facilities name.

Re: Export to Excel - want excel file with multiple sheets according to variable name

Patrick — Sat, 20 Jan 2024 00:37:19 GMT

Your code works for me so there must be something else that's going wrong. Ideally make sure that there is no pre-existing Excel with this name when you run your code (especially not one that you've got open).

data work.test;
  infile datalines truncover dsd dlm=',';
  input (patient Last_Name First_Name) (:$8.) DOB:date9. Quality facility:$10. (ID Contact Result notes Duplicate) (:$8.);
  format dob date9.;
  datalines;
1,LN1,FN1,01JAN2000,1,Facility 1,X,X,X,X,X
2,LN1,FN1,01JAN2000,1,Facility 2,X,X,X,X,X
3,LN1,FN1,01JAN2000,1,Facility 2,X,X,X,X,X
;

ods excel file = "c:\temp\test.xlsx";
ods excel options (sheet_interval="bygroup" sheet_name="#byval(facility)");

proc sort data = work.test;
  by facility Duplicate;
run;

options nobyline;

proc report data = work.test STYLE(Header)={foreground=black background=WHITE};
  by facility;
  columns ('Monthly Report' patient Last_Name First_Name DOB Quality) facility ID Contact Result notes Duplicate;
  define patient / 'Patient Number';
  define Last_Name / 'Last Name';
  define First_Name / 'First Name';
  define DOB / 'Patient DOB';
  define Quality / 'Quality Assurance';

  compute Quality;
    if Quality in ('Late' 'Early')  then call define (_row_,"style","style={background=#a6bddb}");
    if Quality = 'Missed'           then call define (_row_,"style","style={background=#fff7bc}");
  endcomp;

  compute Duplicate;
    if Duplicate = 'yes' then call define (_row_,"style","style={background=#7fcdbb}");
  endcomp;
run;

options byline;
ods excel close;

Re: Export to Excel - want excel file with multiple sheets according to variable name

ark123 — Fri, 16 Feb 2024 22:27:49 GMT

Thank you for assisting with this question