I currently use this process to append multiple datasets to one excel document at any given location:
the two data sets are exported and become two seperate tabs in the "&folder.CPSC_Enrollment__&dt..xls"
%let pc = NYWSWXXXXXX;
%let folder = C:\Users\drountree\;
data _null_;
dt=put( today( ),date9. );
call symput('dt',dt);
run;
proc export
data = Summary_By_Org_HMO
outfile = "&folder.CPSC_Enrollment__&dt..xls"
dbms = excelcs replace;
sheet = "Summary_By_Org_HMO";
SERVER = "&pc";
run;
proc export
data = Summary_By_Org_PPO
outfile = "&folder.CPSC_Enrollment__&dt..xls"
dbms = excelcs replace;
sheet = "Summary_By_Org_PPO";
SERVER = "&pc";
run;
Now this is where it gets fun. I understand fully the section above and I'm able to code multiple datasets
meeting various requirements and have them output to various .XLS on different tabs based on a given criteria.
MY PROBLEM:
I need to take a newly learned process via data_null; (ODS tagsets subset PROC REPORT) and take the output Transposed file
and export each one to the same xls as above based on a given condition.
PROC TRANSPOSE DATA=Empire_X3370 OUT=Empire_X3370 PREFIX=ID_;
SYSECHO "PROC TRANSPOSE DATA=ABRIDGED OUT=ABRIDGED PREFIX=ID_;";
BY CategoryCode CategoryDescription indx;
var Benefit;
id PBP_Number;
idlabel idlabel;
RUN;
ods tagsets.ExcelXP file= '/windows/Infodata/DRountree/CAB/Empire_X3370.xml';
PROC REPORT DATA=Empire_X3370 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
ods tagsets.excelxp close;
The above code creates one data set Empire_X3370 and exports a XML file to the specified location.
I'm creating several different comparissions and need to export Blue_X5590 to the same file but in a different tab.
Keep in mind if this wasn't a PROC REPORT ODS XML output then this would be easy and I could use the first set of code above:
%let pc = NYWSWXXXXXX;
%let folder = C:\Users\drountree\;
data _null_;
dt=put( today( ),date9. );
call symput('dt',dt);
run;
proc export
data = Empire_X3370
outfile = "&folder.CPSC_Enrollment__&dt..xls"
dbms = excelcs replace;
sheet = "Empire_X3370";
SERVER = "&pc";
run;
proc export
data = Blue_X5590
outfile = "&folder.CPSC_Enrollment__&dt..xls"
dbms = excelcs replace;
sheet = "Blue_X5590";
SERVER = "&pc";
run;
The problem is the output from the PROC REPORT...
Hi:
If you build a "big" dataset with multiple BY groups, then 1 PROC REPORT with BY GROUP processing could be used to produce each worksheet in a workbook. That part is easy. If you need extensive data manipulation and/or a PROC REPORT for each comparison, then that moves you into the world of using SAS Macro processing. Here's something about macro processing http://support.sas.com/resources/papers/proceedings13/120-2013.pdf to get you started with that.
I agree with data_null_ however, looking at the options for TAGSETS.EXCELXP might be beneficial to you. For example, without trying to replicate your report, I have just made some fake data to show you how BY group processing could work for you to make separate tabs, as you describe. I can get a sheet for every BY group easily without much trouble. This might require that you make a separate dataset for every comparison you want and use some data manipulation to get you there. But is seems like you have your program working for 1 comparison, so it should not be too troublesome to make a bigger dataset for the other comparisons.
Cynthia
data fakedata;
set sashelp.class;
do grp = 'PlanA_vs_PlanB', 'PlanA_vs_PlanC', 'PlanA_vs_PlanD';
output;
end;
run;
proc sort data=fakedata;
by grp name;
run;
ods tagsets.excelxp file='c:\temp\showby.xml' style=sasweb
options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval1');
proc report data=fakedata nowd;
by grp;
column grp name age sex height weight;
run;
ods _all_ close;
You helped me with the first part of this (Proc Transpose & Proc Report)
The problem is I can't use the output generated from Proc Report - since it's not in the same layout as the FINAL ODS excel file created.
I trouble shooted by adding an OUT= to the Proc Report and this was obviously not the correct direction since the output contained_NAME_, etc and it's not in the same layout as the report or the XML file.
Sorry to pull you into this mess - Enjoy your weekend
PROC REPORT DATA=Empire_X3370 OUT=Empire_X3370 spanrows nowd split='~';
Can you make the other two reports(sheets) created by the two PROC EXPORTS with PROC REPORT? That will allow you to keep the "formatted" sheet you have for the PROC REPORT sheet and add formatting to the other two give them some style. You can convert the XML to XLSX after it is created if that is needed.
Or we can figure a way to make the PROC REPORT report using data steps and PROC EXPORT a bit fiddly and not as nice but doable.
This makes the report using PROC EXPORT. It has the same data as the PROC REPORT EXCELXP but not the nice formatting
Good morning data_null;
I created a mock sample of what I'm trying to accomplish and loaded it to Box.com (sharing site) https://app.box.com/s/mmapprud9sve9v1p77xr
In the mock up you see on one tab PLAN_A vs. PLAN_B and on the second tab you see PLAN_B vs. PLAN_B.
In our prior conversation we solved how to take the data and convert it to a point where I can do a side by side comparison of various health plans per category via TRANSPOSE and PROC REPORT.
Now the additional requirement is to build a complex report with the information based on different scenarios:
IE: One XLS might contain all HMO PLANS for Suffolk County and provide a side by side for PLAN_A vs each competitor. These are separated tab by tab,
which means each competitor has one tab to itself per the comparison with PLAN_A (This logic I'm programming and as stated above was normal done by creating a separate
dataset for each compare then exporting to the same XLS but on a different tab).
The problem is how do I perform the same task with the output from proc report? I don't mind programming each data set with it's own TRANSPOSE/REPORT.
If you think of all the Health Plans in NY - I'm comparing PLAN_A per each County (which creates a separate XLS for each one, Suffolk, Nassau, etc) and within each XLS are all the competitors for PLAN_A. Each one on it's own tab. PLAN_A vs PLAN_B (tab 1) PLAN_A vs PLAN_C (tab 2), etc....
When I look at last year report that was created manually it was 10 XLS documents with over 20 tabs in each XLS.
This is why I need to find a way to automate the output from PROC TRANSPOSE / PROC REPORT into multiple XLS on multiple tabs based on the DATASET I define within my PROC SQL to parse the data.
Pheeeeeeeeeeeeeeeeeeeewwwwwwwwwwwwwwwwwwwwwwwwwwww
Derrick Rountree │ Sr. Business Data Analyst, Medicare Products │ Office (646) 447-5992
I think you just need to look more closely at what can be accomplished with TAGSETS.EXCELXP like multi-sheet workbooks. Look at http://www.lexjansen.com/ for papers by various authors.
Hi:
If you build a "big" dataset with multiple BY groups, then 1 PROC REPORT with BY GROUP processing could be used to produce each worksheet in a workbook. That part is easy. If you need extensive data manipulation and/or a PROC REPORT for each comparison, then that moves you into the world of using SAS Macro processing. Here's something about macro processing http://support.sas.com/resources/papers/proceedings13/120-2013.pdf to get you started with that.
I agree with data_null_ however, looking at the options for TAGSETS.EXCELXP might be beneficial to you. For example, without trying to replicate your report, I have just made some fake data to show you how BY group processing could work for you to make separate tabs, as you describe. I can get a sheet for every BY group easily without much trouble. This might require that you make a separate dataset for every comparison you want and use some data manipulation to get you there. But is seems like you have your program working for 1 comparison, so it should not be too troublesome to make a bigger dataset for the other comparisons.
Cynthia
data fakedata;
set sashelp.class;
do grp = 'PlanA_vs_PlanB', 'PlanA_vs_PlanC', 'PlanA_vs_PlanD';
output;
end;
run;
proc sort data=fakedata;
by grp name;
run;
ods tagsets.excelxp file='c:\temp\showby.xml' style=sasweb
options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval1');
proc report data=fakedata nowd;
by grp;
column grp name age sex height weight;
run;
ods _all_ close;
For a new jack this is quite the challenge - but I love learning so it's ok.
Once I'm done reading up on both suggestions I'll provide an update and close this one out.
Thank you so much data_null and Cynthia...
You two are great - Thanks for all your help and guidance.
Report is about 80% done and once complete I'll look into macro processing to clean up the repetitive section of code.
Who would of thought it was as easy as placing the report code inside the ODS open and close.
Anything I place in between is added to one Excel workbook, so now I'm reading up on naming the tabs based on the datasheet that imported.
HI,
Here's an example of naming the sheets. And here's a paper about getting started with SAS Macro variables and Macro program definitions: http://support.sas.com/resources/papers/proceedings13/120-2013.pdf
Cynthia
title; footnote;
ods tagsets.excelxp file='c:\temp\multsheet.xml'
options(doc='Help') style=sasweb;
ods tagsets.excelxp options(sheet_name='Females');
proc print data=sashelp.class;
where sex = 'F';
run;
ods tagsets.excelxp options(sheet_name='Males');
proc print data=sashelp.class;
where sex = 'M';
run;
ods tagsets.excelxp options(sheet_name='Boot Sales');
proc print data=sashelp.shoes;
where product='Boot';
run;
ods tagsets.excelxp options(sheet_name='Eastern Canada');
proc print data=sashelp.prdsale;
where product = 'DESK' and region = 'EAST' and country='CANADA';
run;
ods _all_ close;
I wasn't able to use the #byval function since I removed the BY from my Proc Report. I still need to learn (MACROS) so the redundant code below would be written once and call for each of the DATA= and create the .XML using the correct names from each DATASET.
ODS TAGSETS.EXCELXP file='/windows/Infodata/DRountree/CAB/Competitive_Analysis_HMO_Nassau.xml'
options(sheet_interval='table' suppress_bylines='yes' sheet_name='#byval1');
PROC REPORT DATA = Empire_H3370 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
PROC REPORT DATA = Healthfirst_H3359 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
PROC REPORT DATA = HealthPlus_H6181 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
PROC REPORT DATA = Liberty_H3337 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
PROC REPORT DATA = VNSNY_H5549 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
PROC REPORT DATA = WellCare_H3361 spanrows nowd split='~';
columns CategoryCode CategoryDescription ID_:;
Define CategoryCode / order order=internal noprint;
define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';
define ID_: / display;
compute after CategoryCode;
line ' ';
endcomp;
run;
ODS _ALL_ CLOSE;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.