topic Re: MULTIPLE PROC REPORT OUTPUTS in SAS Programming

MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Fri, 22 Aug 2014 17:38:46 GMT

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...

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Fri, 22 Aug 2014 19:20:11 GMT

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='~';

Re: MULTIPLE PROC REPORT OUTPUTS

data_null__ — Fri, 22 Aug 2014 19:43:06 GMT

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.

Re: MULTIPLE PROC REPORT OUTPUTS

data_null__ — Fri, 22 Aug 2014 20:57:15 GMT

This makes the report using PROC EXPORT. It has the same data as the PROC REPORT EXCELXP but not the nice formatting

filename FT15F001 temp lrecl=512; 
data hipa;
   infile FT15F001 dsd dlm='|'; 
   length Contract_ID $20 CategoryDescription $64 CategoryCode 8 Benefit $256; 
   input (_all_)(:);
   parmcards4; 
X12345-145-01|Premium and Other Important Information|1|$0 monthly plan premium in addition to your monthly Medicare Part B premium.
X12345-145-01|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
X12345-145-01|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X12345-145-01|Doctor and Hospital Choice|2|You must go to network doctors specialists and hospitals.
X12345-145-01|Doctor and Hospital Choice|2|Referral required for network specialists (for certain benefits).
R5678-012-01|Premium and Other Important Information|1|$25.00 monthly plan premium in addition to your monthly Medicare Part B premium.
R5678-012-01|Premium and Other Important Information|1|Most people will pay the standard monthly Part B premium in addition to their MA plan premium.
R5678-012-01|Premium and Other Important Information|1|Some physicians providers and suppliers that are out of a plan's network (i.e. out-of-network) accept "assignment" from Medicare and will only charge up to a Medicare-approved amount.
X5678-888-00|Premium and Other Important Information|1|$3 400 out-of-pocket limit for Medicare-covered services.
X5678-888-00|Premium and Other Important Information|1|$5 100 out-of-pocket limit for Medicare-covered services.
X5678-888-00|Doctor and Hospital Choice|2|No referral required for network doctors specialists and hospitals.
X5678-888-00|Doctor and Hospital Choice|2|You can go to doctors specialists and hospitals in or out of the network. It will cost more to get out of network benefits.
;;;;
;;;;

*'; 
data set1 ;
   input (CONTRACT_ID PLAN_NAME) (:$20.);
   cards; 
R5678-012-01 PLAN_B
X12345-145-01 PLAN_A
X5678-888-00 PLAN_B
;;;;

data set2 ;
   input (CONTRACT_ID ENROLLMENT) (:$20. :F12.);
   cards; 
R5678-012-01 112000
X12345-145-01 111000
X5678-888-00 112000
;;;;

data set3 ;
   input (CONTRACT_ID STAR_RATING) (:$20.);
   cards; 
R5678-012-01 3.5 
X12345-145-01 4
X5678-888-00 5
;;;;

proc sort data=hipa;
   by contract_id categorycode;
   run; 
data hipa;
   merge hipa set1-set3;
   by contract_id;
   run; 
data hipa;
   set hipa;
   by contract_id categoryCode;
   if first.categoryCode then indx=0; 
   indx+1; 
   length idlabel $128; 
   idlabel = catx('~',plan_name,vvalue(Enrollment),star_rating,contract_id);
   format enrollment comma12.; 
   run; 
proc sort data=hipa;
   by categoryCode indx Contract_ID;
   run; 
proc transpose data=hipa out=hippo prefix=ID_;
   by CategoryCode CategoryDescription indx;
   var Benefit;
   id Contract_ID;
   idlabel idlabel;
   label CategoryDescription="Plan Name~Enrollment~Star Rating~Category Description"; 
   run; 

*ods tagsets.ExcelXP file='~/hippo.xml'; 
proc report data=hippo spanrows nowd split='~' out=hipreport; 
   columns CategoryCode CategoryDescription ID_:;
   Define categoryCode / order order=internal noprint; 
   define CategoryDescription / order 'Plan Name~Enrollment~Star Rating~Category Description'; 
   define ID_: / display; 
   compute after categorycode;
      line ' '; 
      endcomp; 
   run; 
*ods tagsets.excelxp close;
proc print data=hipreport;
   run; 
data hipreport;
   if 0 then set hipreport;
   array _id id_:;
   if _n_ eq 1 then do; 
      do j = 1 to 4; 
         do i = 1 to dim(_id);
            _id = scan(vlabel(_id),j,'~');
            end; 
         CategoryDescription = scan(vlabel(CategoryDescription),j,'~');
         output; 
         end; 
      call missing(of _all_);
      output; 
      end; 
   set hipreport;
   by CategoryCode;
   if not first.CategoryCode then call missing(CategoryDescription);
   output; 
   drop CategoryCode _break_ i j;
   run; 
proc print; 
   run; 
data hipview / view=hipview;
   set hipreport;
   attrib _all_ label='a0'x; 
   run; 
proc export outfile='~/hipexport' dbms=xlsx data=hipview label replace; 
   run;

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Mon, 25 Aug 2014 13:19:20 GMT

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

Re: MULTIPLE PROC REPORT OUTPUTS

data_null__ — Mon, 25 Aug 2014 14:56:13 GMT

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.

Re: MULTIPLE PROC REPORT OUTPUTS

Cynthia_sas — Mon, 25 Aug 2014 15:46:20 GMT

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;

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Mon, 25 Aug 2014 16:04:01 GMT

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...

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Wed, 27 Aug 2014 12:11:43 GMT

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.

Re: MULTIPLE PROC REPORT OUTPUTS

Dsrountree — Wed, 27 Aug 2014 14:15:34 GMT

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;

Re: MULTIPLE PROC REPORT OUTPUTS

Cynthia_sas — Thu, 28 Aug 2014 01:02:27 GMT

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;