Dynamically Creating Bar Charts

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Dynamically Creating Bar Charts

I am trying to figure out if there is a way to dynamically create bar charts based on a given input.  Currently my code generates summary tables based on certain identifiers in the input data.  In addition to the tables, I would like to create two bar charts for each code as well.  I can get the tables to generate dynamically through a macro I have created but I do not know how or if it is possible to generated these bar charts dynamically as well?  Any thoughts or suggestions would be greatly appreciated!


Accepted Solutions
Solution
‎05-27-2015 04:09 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Dynamically Creating Bar Charts

Hi,

Rather than the code, which is a bit over complicated, perhaps show an example of what you have (test data in a datastep) and what you need out.  It looks like you want to create one graph and three prints, and put these to an Excel grid.  This should be straightforward as:

proc sql noprint;

  create table LOOP as

  select distinct BP_Billing_Prov_Num_OSCAR

  from provider;

quit;

ods tagsets excelxp file="...xml";

data _null_;

     set loop;  /* For each row, which is a loop variable do */

     call execute('ods tagsets.excelxp options=(sheet_name="'||strip(BP_Billing_Prov_Num_OSCAR)||'");

                        proc sgplot ....; run;

                        proc print data=...; run;

                        proc print data=...; run;');

run;

ods tagsets.excelxp close;

                   

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Dynamically Creating Bar Charts

Hi,

Yes, with proc template you can have dynamic variables defined in the template:

Here: http://support.sas.com/documentation/cdl/en/grstatug/63302/HTML/default/viewer.htm#n0c1svsedk2ptcn1g...

and: http://support.sas.com/documentation/cdl/en/grstatproc/62603/HTML/default/viewer.htm#a003239624.htm

There are also other ways of doing it, you could for instance generate the code from your dataset with a call execute.  Use a macro as you mentioned. 

SAS Super FREQ
Posts: 3,406

Re: Dynamically Creating Bar Charts

I think we need more infomation about what you are trying to do.  If your macro generates summary tables, why not just call PROC SGPLOT directly from the macro?

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

I am generating the same tables (includes counts and summaries of various attributes) for various given Identifier codes, and writing each table to a separate excel worksheet. I am not sure how to generate bar charts, further summarizing, and assigning to its respective worksheet.  I am also not sure without writing over the previous graph if this is possible in PROC SGPLOT.  I will post my code later when I am at my main computer to further clear up confusion.

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

Sorry, I was in a class all last week and unable to post my code.  Anyways here is a snip it of my code.  I have excluded how the prov_&x and revcd_&x tables are generated to keep this as condensed as possible while still relaying what I am trying to do. I have tried using the Name function and gout function but keeping error messages in my log.  I can see in my results section that graphs are being generated in SAS but exporting them to the excel sheets is where I am running into issues. 

%macro runtab(x);

/*Create summary chart for generating graph of codes billed per month*/

proc sql;

CREATE TABLE summary_&x AS

select DISTINCT month, COUNT (CH_ICN) AS ICN_Count, CLI_Revenue_Cd_Category_Cd

FROM corf_data1_sorted

WHERE BP_Billing_Prov_Num_OSCAR=&x

group by month ,CLI_Revenue_Cd_Category_Cd;

run;

/*Create a graph of Services Per Month and group by the Revenue Code*/

proc sgplot data=summary_&x NAME= 'graph_&x';

  title 'Provider Revenue Analysis';

  vbar month / response=ICN_count group=CLI_Revenue_Cd_Category_Cd stat=sum

       datalabel datalabelattrs=(weight=bold);

  yaxis grid  label='Month';

  run;

%mend runtab;

/*Create a macro variable of all the codes */

proc sql noprint;

  select BP_Billing_Prov_Num_OSCAR

  into :varlist separated by ' ' /*Each code in the list is sep. by a single space*/

from provider;

quit;

%let cntlist = &sqlobs; /*Store a count of the number of oscar codes*/

%put &varlist; /*Print the codes to the log to be sure our list is accurate*/

/*write a macro to generate the output tables*/

%macro output(x);

ods tagsets.excelxp options(sheet_interval='none' sheet_name="&x");

proc print data=prov_&x;

run;

proc print data=prov_revcd_&x;

run;

proc print data=graph_&x;

run;

%mend;

/*Run a loop for each oscar code. Each code will enter the document generation loop*/

%macro loopit(mylist);

    %let else=;

   %let n = %sysfunc(countw(&mylist)); /*let n=number of codes in the list*/

    data

   %do I=0 %to &n;

      %let val = %scan(&mylist,&I); /*Let val= the ith code in the list*/

    %end;

   %do j=0 %to &n;

      %let val = %scan(&mylist,&j); /*Let val= the jth code in the list*/

/*Run the macro loop to generate the required tables*/

%runtab(&val);

%output(&val);

   %end;

   run;

%mend;

/*Run the macro loop over the list of significant procedure code values*/

ods tagsets.excelxp file="W:\user\test_wkbk.xml";

%loopit(&varlist)

ods tagsets.excelxp close;

Solution
‎05-27-2015 04:09 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Dynamically Creating Bar Charts

Hi,

Rather than the code, which is a bit over complicated, perhaps show an example of what you have (test data in a datastep) and what you need out.  It looks like you want to create one graph and three prints, and put these to an Excel grid.  This should be straightforward as:

proc sql noprint;

  create table LOOP as

  select distinct BP_Billing_Prov_Num_OSCAR

  from provider;

quit;

ods tagsets excelxp file="...xml";

data _null_;

     set loop;  /* For each row, which is a loop variable do */

     call execute('ods tagsets.excelxp options=(sheet_name="'||strip(BP_Billing_Prov_Num_OSCAR)||'");

                        proc sgplot ....; run;

                        proc print data=...; run;

                        proc print data=...; run;');

run;

ods tagsets.excelxp close;

                   

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

That is correct, I want two tables and one graph printed per excel sheet, for each distinct ID (bp_billing_provider) that comes up in the original data.  So one table would would look something like this:

Obs    ID      Bill_Cd      COUNT     Payment Total      PCT

1    abc123       x1            17         0.00       $45170.99     0.00

2    abc123       x2          388     44797.98        $45170.99    99.17

3    abc123       y1            1       147.65        $45170.99     0.33

4    abc123       y2            15       225.36       $45170.99     0.50

  THE SECOND TABLE SOMETHING LIKE:

            

         Obs      ID       Rev_Cd   COUNT     Payment        Total      PCT

          1       abc123           42           23      6705.66    $45170.99    14.85

          2       abc123            43          199     38465.33    $45170.99    85.15

And then the bar chart breaking down payment by month, subgrouped by rev_cd.  Is this sort of what you were asking for? Thanks.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Dynamically Creating Bar Charts

Yes, so the code I posted should work.  You will need to add in you print and sgplot procedures.

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

Thank you, this is a much more cleaner and concise way about this, the only problem is I am getting the "ERROR: No body file. TAGSETS.EXCELXP output will not be created." error.  I have pointed to a variety of paths and continue to get this, any suggestions on that ?

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Dynamically Creating Bar Charts

Only thing I can remember this error being associated with is file or path invalid or missing.  Post an example code which is causing this.

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

Sure here is just my opening ODS code:

ods tagsets excelxp file="C:\Users\Desktop\test\sample.xml" ;

Here is the error message:

1   + ods tagsets.excelxp options(sheet_name="144527");  proc print

data=data1 ;    BY Bill_Type;

    Procedure;

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

180: LINE and COLUMN cannot be determined.

ERROR 180-322: Statement is not valid or it is used out of proper order.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Dynamically Creating Bar Charts

Well first off you are missing a dot between tagsets and excelxp:

ods tagsets.excelxp file="C:\Users\Desktop\test\sample.xml" ;


The next thing to do is to copy the code out and just write a basic statement with that code, point to sashelp dataset or something.  If the file writes out fine from that then there is something missing from the code, can't tell from that snippet.  If it doesn't work in just a basic environment, maybe there is something else.  Try narrowing it down.

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

Thank you, I can write the tables to excel that way but from research I've done it appears it is not possible with SAS 9.3, to print graphs to Excel using the ODS ExcelXP tagset.  Nonetheless the call execute was a great suggestion and will definitely keep that handy!

Grand Advisor
Posts: 10,210

Re: Dynamically Creating Bar Charts

Since the tables are Summary data, and presumably do not need additional manipulation, then pick another ODS destination such as RTF or PDF that will include graphics.

Occasional Contributor
Posts: 16

Re: Dynamically Creating Bar Charts

Yes, that works, thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 501 views
  • 3 likes
  • 4 in conversation