BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
acordes
Rhodochrosite | Level 12

The code generates the output I want except that for any PROC FREQ call the HTML file is overwritten.

But I want it to become appended.

How can I achieve this?

 

%LET LIST = *MISSING-;* INITIALIZE FOR NO ROWS SELECTED;

PROC SQL; SELECT CAT("ods html FILE='S:\COMITES Valores Residuales 2018\insights analytics\LCV EFECTO FILTRO MUESTRA.htm'(NOBOT)
nogtitle gpath='S:\COMITES Valores Residuales 2018\insights analytics\'  ;
ODS GRAPHICS ON/ IMAGEMAP=ON imagename='TESTA'; TITLE ", STRIP(CONCAT_D),"; PROC FREQ DATA=WORK.LCVQ1; WHERE (", CONCAT, " * (BRAND='VW LCV'));
TABLE MODELEUROTAX*APLAZ / OUT=FREQ_TWO",N,
" PLOTS=MOSAICPLOT NOROW NOCUM NOCOL NOPERCENT; RUN; ODS HTML CLOSE;") LENGTH=5000
INTO :LIST SEPARATED BY " "
FROM WORK.CLAUSATOR_REL
;
QUIT;

%PUT &LIST;
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  I don't see that your code is allowing an append. It looks like you're re-using that name every time you would run the code so the file would get overwritten. With just using SASHELP files, here's an example of how to do an append with HTML:

filename ht_app 'c:\temp\myfile.html' mod;
 
** make first file want top html for this one;
ods html file=ht_app (nobot);
 proc print data=sashelp.class;
title 'from first step';
run;
ods html close;
 
** append second file do not want top or bottom HTML in this one;
ods html file=ht_app(notop nobot);
proc freq data=sashelp.class;
title 'from second step';
tables sex;
run;
ods html close;
 
** append third and final file want bottom html in this one;
ods html file=ht_app (notop);
proc means data=sashelp.class;
  var height;
  class age;
  title 'from third step';
run;
ods html close;

You must use a FILENAME statement with MOD for the file reference, or else the file gets overwritten with each ODS HTML invocation.

 

It's not clear what your PROC SQL is doing or what is in the file you have in the FROM. So a better explanation of what you need and why you are using %PUT. If you need to generate some statements based on data in a SAS dataset, you'd be better off writing a macro program definition and passing it the variables that change, in a DATA step and/or a CALL EXECUTE instead of what you appear to be trying.

 

Cynthia

View solution in original post

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Try again.  Start by posting some test data, in the form of a datastep, which shows what claustor_rel looks like.  Then show what you want out at the end.  All this sql appears to be doing is printing to the output windows at least one string with some bits concatenated, and creating a macro variable (very bad idea putting code into macro variables)??  Nothing to do with proc freq, or html or appending?

acordes
Rhodochrosite | Level 12

Thanks for your quick reply and your recommendations. But this time it shouldn’t matter so much BECAUSE if I replace the output for an pdf output I get an pdf file that contains the output for all PROC FREQ calls. I am interested in getting the mosaic plot according to every PROC FREQ I run. It works for podf but it doesn’t work for html.

Here I use the concatenate within PROC SQL to run several PROC FREQ applying different filter to the data.

I do not feel comfortable to share the raw data and I don’t want to spend time to mask it.

So I apply it to a test data set which I will post later.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Without any information I can't help.  PDF and HTML files are very different file types.  In PDF your output is getting rendered to a complete PDF file, if you run it to a HTML then it is combinations of text files, pictures and other bits.  

In the example you give, you are opening and closing the destination every iteration, which I suspect isn't what you intend.  What you would be better off doing is to simplfy your code down to something like:

ods html...;

%macro Create_Graph (subset=);
...
%mend Create_Graph;

data _null_;
  set ...;
  call execute(cats('%Create_Graph (subset=',...,');'));
run;

ods html close;

So that all the output is within the ods open and close.

Cynthia_sas
SAS Super FREQ

Hi:

  I don't see that your code is allowing an append. It looks like you're re-using that name every time you would run the code so the file would get overwritten. With just using SASHELP files, here's an example of how to do an append with HTML:

filename ht_app 'c:\temp\myfile.html' mod;
 
** make first file want top html for this one;
ods html file=ht_app (nobot);
 proc print data=sashelp.class;
title 'from first step';
run;
ods html close;
 
** append second file do not want top or bottom HTML in this one;
ods html file=ht_app(notop nobot);
proc freq data=sashelp.class;
title 'from second step';
tables sex;
run;
ods html close;
 
** append third and final file want bottom html in this one;
ods html file=ht_app (notop);
proc means data=sashelp.class;
  var height;
  class age;
  title 'from third step';
run;
ods html close;

You must use a FILENAME statement with MOD for the file reference, or else the file gets overwritten with each ODS HTML invocation.

 

It's not clear what your PROC SQL is doing or what is in the file you have in the FROM. So a better explanation of what you need and why you are using %PUT. If you need to generate some statements based on data in a SAS dataset, you'd be better off writing a macro program definition and passing it the variables that change, in a DATA step and/or a CALL EXECUTE instead of what you appear to be trying.

 

Cynthia

acordes
Rhodochrosite | Level 12

Although I admit your and RW9's critics and and can see that it's much cleaner to wrap in a macro call, my hardly understandable code works "well" (except the output issue) and with your approach I am getting closer to append it in one html file. 

Now playing with NOBOT and NOTOP I can get up to 3 outputs in one html file. 

But actually here I have 24 scenarios and I wonder if there's a magical keyword that appends all without controlling for the last and the first in my code?

 

FILENAME HT_APP 'S:\COMITES Valores Residuales 2018\insights analytics\LCV EFECTO FILTRO MUESTRA.htm' MOD;
ODS HTML  FILE=HT_APP(NOBOT) nogtitle gpath='S:\COMITES Valores Residuales 2018\insights analytics\'  ;
ODS GRAPHICS ON/ IMAGEMAP=ON imagename='TESTA';
%PUT &LIST;

ODS HTML CLOSE;

 

OUTPUT STACKED.png

Cynthia_sas
SAS Super FREQ
Sorry, there's no magic option. You have to control notop and/or nobot for each invocation, which is what makes a macro program a better choice, because you can use macro logic to control option for each step.
Cynthia
acordes
Rhodochrosite | Level 12

Thanks Cynthia and @RW9

 

Combining your valuable answers I could finally resolve it. Instead of treating the last and the first of the outputs I add one hard-coded first ods output and one hard-coded last.

 

FILENAME HT_APP 'S:\COMITES Valores Residuales 2018\insights analytics\LCV EFECTO FILTRO MUESTRA.htm' MOD;
ODS GRAPHICS ON/ IMAGEMAP=ON imagename='TESTA';

ODS HTML FILE=HT_APP (NOBOT) GPATH='S:\COMITES Valores Residuales 2018\insights analytics\';
TITLE ALL;
PROC FREQ DATA=WORK.LCVQ1;
TABLE ME_EXT*APLAZ / OUT=FREQ_TWO_NO_FILTER PLOTS=MOSAICPLOT NOROW NOCUM NOCOL NOPERCENT;
RUN;
ODS HTML CLOSE;

%macro Create_Graph (subset=, ID=, FILTRO=);

ODS HTML FILE=HT_APP (NOTOP NOBOT) GPATH='S:\COMITES Valores Residuales 2018\insights analytics\';
TITLE &FILTRO;
PROC FREQ DATA=WORK.LCVQ1;
WHERE ⊂
TABLE ME_EXT*APLAZ / PLOTS=MOSAICPLOT NOROW NOCUM NOCOL NOPERCENT;
RUN;
ODS HTML CLOSE;

%mend Create_Graph;

data _null_;
  set WORK.CLAUSATOR_REL;
/*  BY FILA;*/
/*  IF NOT (FIRST.FILA OR LAST.FILA) THEN DO;*/
  call execute(cats('%Create_Graph (subset= ', STRIP(CONCAT), ', ID= ', N,
', FILTRO= ', STRIP(CONCAT_D),');'));
/*  END;*/
run;

ODS HTML FILE=HT_APP (NOTOP) GPATH='S:\COMITES Valores Residuales 2018\insights analytics\';
TITLE APLAZAMIENTOS;
PROC FREQ DATA=WORK.LCVQ1;
TABLE APLAZ / OUT=FREQ_ONE PLOTS=MOSAICPLOT NOROW NOCUM NOCOL NOPERCENT;
RUN;
ODS HTML CLOSE;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Great, maybe mark @Cynthia_sas's answer correct.  Also, maybe avoid coding all in Shouting case.

Bottlebrush000
Calcite | Level 5

Hi,

Thanks so much for the sample solution provided. I tried it on ODS HTML output and it works like a charm. 

However, when I use the same approach to send the appended output directly to email via "filename xx email " options, this did not work. 

 

My ODS code has a data step and a proc report. 

The code is sending two separate emails (1) with the datastep output (2) proc report output. 

The filename/email doesnot support notop/nobot options. 

 

Did anyone come across this situation ? 

 

Cynthia_sas
SAS Super FREQ
Hi:
I would make an attachment of the PROC REPORT output and then use the ATTACH= option to send the file. Look at the example on page 7 of this page: http://support.sas.com/resources/papers/proceedings11/300-2011.pdf -- the basic concept is the same. My attachment in the paper used ODS RTF, but could have been any other ODS destination. And the PROC PRINT could have been PROC REPORT or any SAS procedure that produced output.

Cynthia
Bottlebrush000
Calcite | Level 5
Hi Cynthia,

I ended up writing ods output to a temp file work location and subsequently rewriting the file to email filename using data step null.

Thanks
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please do not re-open old answered questions from years back.  Create a new topic and post all the relevant information.

 

What I will say from what you post - and this is something I always say - email is not the tool for delivering reports or data.  It is unsecure and limited.  Use SFTP, Web Portal, Or other reporting solution.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2716 views
  • 2 likes
  • 4 in conversation