URL link in proc report, how to use alias for links stored in a variable for all records

[piddy_]

Hi 

I hope somebody can help me. I have tried everything but can't seem to get it to work.

I have a dataset that contains information about all SAS logs from night to morning and the end result is sent by email with proc report. The link for the individual logs are stored in the variable "Link" in the dataset.

I can create the proc report table but its shows the entire path for the link. I would really like it to just show "Link to log" or something like that for all the individual links, so the table doesn't become to wide.

I have attached a screenshot of the proc report and how it looks like in Outlook, the SAS code and the dataset.

If someone also can say why the variable that shows number of errors in the logs (“Antal fejl”) is all missing I would be glad. And is there some way to get the colored area in the first column, to be seen as "one cell" pr. error type, so there is no space between the different rows? 

Kind regards

Thomas

SAS code:

filename mail email
      /*Til test*/
      to          = (&program_ansvarlig_mail) 
  /*  to           = (&program_to_mail) 
      cc           = (&program_cc_mail)   */
      bcc          = ("")
      subject      = "Log oversigt"
      from         = &program_ansvarlig_mail
      importance   ="HIGH"
      content_type ="text/html";

ods _all_  close;
ods html body=mail;

options LEFTMARGIN=0.1in RIGHTMARGIN=0.1in TOPMARGIN=0.1in BOTTOMMARGIN=0.1in papersize=(30in 24in) orientation=landscape  nodate ;

title1      justify=left height=6pt  "<i> <b> Oversigt over logs </b> </i>";
title2      " ";
title3      " ";
footnote1   justify=left height=3pt  "<a href='file:///&logfilmappe'>Link til mappen med alle logfiler.</a>";

      proc report data=samlet_logs_mail_3 spanrows headskip nowindows nocenter spacing=1 split='\'
      
      style(report)=[cellspacing=5 borderwidth=10 bordercolor=blue fontsize=11 vjust=b]
      style(header)=[background=DAGRAY color=WHITE fontweight=bold fontsize=12 vjust=b]
      style(column) =[fontsize=8pt ] ;
      
      col ('Oversigt over kørsler' Fejl Lognavn /*Ref*/ Link Startdato Starttid Slutdato Sluttid Kørselstid Filstr_Mb Antal_programmer Programnavn Fejltype Antal_fejl);

         Define Fejl            / 'Status'  order order=data;
         
         compute Fejl           /CHAR;
             if Fejl in("Fejl i log")       then call define(_col_,'style','style=[background=STPK]');
             if Fejl in("Log > 50 Mb")      then call define(_col_,'style','style=[background=LIPK]');
             if Fejl in("Ingen fejl i log") then call define(_col_,'style','style=[background=BILG]');
         endcomp;    
         
         define  Lognavn        / group 'Lognavn' style(header)={just=left vjust=b}        width=400 ;
         define link            / "Link til log" group;
 
         compute Link;
              call define(_col_,'URL',link) ;
              call define(_col_,'STYLE','style={url="Tryk for at se logLink"}');
         endcomp;
 
         define Startdato            /  group 'Start dato'        style(header)={just=center vjust=b}      width=25 ;
         define Starttid             /  group 'Start tid'         style(header)={just=center vjust=b}      width=25 ;
         define Slutdato             /  group '**bleep** dato'         style(header)={just=center vjust=b}      width=25 ;
         define Sluttid              /  group '**bleep** tid'          style(header)={just=center vjust=b}      width=25 ;
         define Kørselstid           /  group 'Kørselstid'        style(header)={just=center vjust=b}      width=25 ;
         define Filstr_Mb            /  group 'Filstørrelse, Mb'  style(header)={just=center vjust=b}      width=25 ;
         define Antal_programmer     /  'Antal programmer'  style(header)={just=center vjust=b}      width=15 ; 
         define Programnavn          /  'Programnavn'       style(header)={just=center vjust=b}      width=25 ;
         define Fejltype             /  'Fejltype'          style(header)={just=center vjust=b}      width=25 ;   
         define Antal_fejl           /   'Antal fejl'        style(header)={just=center vjust=b}      width=25 ;   
    run;

ods html close;
ods _all_ close;
  

[piddy_]

Hi again

I found a solution for the link alias with the following:

data samlet_logs_mail_3 (drop=Link_old);
set samlet_logs_mail_2 (rename=(Link=Link_old));
Link        =catt('<a href="',substr(Link_old, 1, length(Link_old)-4),'.log">',"Link til log",'</a>'); 
output;
run;

Are there anyone who can answer me on my other two questions?:

If someone also can say why the variable that shows number of errors in the logs (“Antal fejl”) is all missing I would be glad. And is there some way to get the colored area in the first column, to be seen as "one cell" pr. error type, so there is no space between the different rows? 

[Cynthia_sas · Accepted solution]

Hi:
SPANROWS on the PROC REPORT statement should cause your group or order cells to be merged as you want. The Antal_fejil cell seems to be coming from the data and is not being changed by PROC REPORT. -- so you'll have to figure out why that's not showing. Since you didn't post any data, no one can run your code.

Also some general comments -- options like HEADLINE, HEADSKIP, SPACING and WIDTH= are respected only by ODS LISTING destination (the Output window), these options are totally ignored by ODS HTML, ODS PDF, ODS EXCEL and ODS RTF destinations.

System options like the margin settings and papersize and orientation are also ignored by ODS HTML and so they are not hurting anything here, but not impacting anything either.

I also think it is better to ALWAYS use a unit of measure (for example, PT) for font size -- you have mixed font sizes -- 11, 12 and 8pt -- you should probably specify a unit of measure each time you set fontsize instead of taking the default unit of measure.

Hope this helps,
Cynthia

[Cynthia_sas]

Hi:

  I always recommend a computed variable or some other variable to which the URL can be applied. Right now, you are assigning the URL to the entire text string. Something like this:

Hope this helps,

Cynthia

[piddy_]

Hi Cynthia
Thanks for the two replies. I did post data on my first post.

I tried the solution above, with a computed variable. One thing I couldn't solve was to group the computed variable. I could do that in my own solution. If you see the screenshot of the report, if there are multiple lines pr. log/link etc. it should only show the link one time.

[Cynthia_sas]

Hi:

  I would have expected you to get the note in the log about GROUP usage:

  It's not clear to me which variable you want to have grouped -- since you have so many of them grouped. And, when I look at the data you posted with PROC CONTENTS, the 10 rows are NOT sorted, so I don't understand how your ORDER for Status or your GROUPS are going to work if you want order=data for the first variable on the report row.

Cynthia

[piddy_]

Hi Cynthia.

Thanks for all your insight. This is my final code I think and I'm quite pleased with it now. I created some alias variables to sort the table properly. 

The variabel Sorterings_var, is 1 for error logs, 2 for logs>50 Mb and 3 for logs with no errors because that's how I would like the variable "Fejl" to be sortet.

Depending on if there are any errors in the logs the macro %keep will kick in and include specific variables which will no be present if there are no errors at all in any logs.

See screenshots of the table in outlook to see how it looks like.

I have included the code below.

Is there anything else I can optimize or code that doesn't make sense because it's ods html?

filename mail email
    to           = (&program_ansvarlig_mail)    /*Til test*/
  /*  to           = (&program_to_mail) 
      cc           = (&program_cc_mail)       */
      bcc          = ("")
      subject      = "&emnetekst."
      from         =  &program_ansvarlig_mail
      importance   =  &mail_vigtighed.
      content_type = "text/html";

ods _all_  close;
ods html body=mail;

options nodate missing="";

title1      justify=left height=14  "<i> <b> Oversigt over logs </b> </i>";
title2      " ";
footnote1   justify=left height=8  "<a href='file:///&logfilmappe'>Link til mappen med alle logfiler.</a>";

      proc report data=samlet_logs_mail_4 /*spanrows*/ nowd nowindows nocenter  split='\'
      
      style(report) =[fontsize=12 borderwidth=1 bordercolor=black  vjust=b]
      style(header) =[fontsize=12 background=dagray color=white fontweight=bold  vjust=b]
      style(column) =[fontsize=12 ]
      style(summary)=[fontsize=14 fontweight=bold  vjust=b textalign=r]
      style(lines)  =[fontsize=14 fontstyle=italic fontweight=bold ];
      
      col ("Oversigt over kørsler" Sorterings_var Fejl Startdato=Startdato_sort Starttid=Starttid_sort  Lognavn Link Startdato Starttid Slutdato Sluttid Kørselstid Filstr_Mb Antal_programmer &keep_var) ;

         /*----- Sorteringsvariable, som ikke vises i output ------*/
         define Sorterings_var       / order order=internal noprint;
         define Startdato_sort       / order order=internal noprint; 
         define Starttid_sort        / order order=internal noprint;
        
         Define Fejl                 /  group   "Status"           style(header)=[borderwidth=0.1];
         
         compute Fejl                /  char;
             if Fejl in("Fejl i log")       then call define(_col_,'style','style=[background=STPK]');
             if Fejl in("Log > 50 Mb")      then call define(_col_,'style','style=[background=LIPK]');
             if Fejl in("Ingen fejl i log") then call define(_col_,'style','style=[background=BILG]');
         endcomp;    
         
         define Lognavn              /  group   "Lognavn"         style(header)={just=center vjust=b}      width=200 ;
         define link                 /  group   "Link til log"                                             width=200 ;
         define Startdato            /  group   "Start dato"      style(header)={just=center vjust=b}      width=25  ;
         define Starttid             /  group   "Start tid"       style(header)={just=center vjust=b}      width=25  ;
         define Slutdato             /  group   "**bleep** dato"       style(header)={just=center vjust=b}      width=25  ;
         define Sluttid              /  group   "**bleep** tid"        style(header)={just=center vjust=b}      width=25  ;
         define Kørselstid           /  group   "Kørselstid"      style(header)={just=center vjust=b}      width=25  ;
         define Filstr_Mb            /  group   "Filstr., Mb"     style(header)={just=center vjust=b}      width=25  ;
         define Antal_programmer     /          "# programmer"    style(header)={just=center vjust=b}      width=15  ; 
       
         %macro keep;
         %if &Antal_fejl>0 %then %do;
    
         define Programnavn          /  "Fejl-program"    style(header)={just=center vjust=b}      width=25 ;
         define Fejltype             /  "Fejltype"        style(header)={just=center vjust=b}      width=25 ;   
    
         %end;
         %mend keep;
         %keep;
         
         rbreak after / summarize skip;
    
         %macro keep;
         %if &Antal_fejl>0 %then %do;
    
         compute after;
             line "Der er samlet" antal_fejl.sum commax14.0 " fejl i alle logs.";
         endcomp;
   
         break after Fejl /;
       
         compute after Fejl / style={color=ltgray background=ltgray fontsize=.1pt cellheight=.1pt};
           line "";
         endcomp;
         
         %end;
         %mend keep;
         %keep;
    run;

ods html close;
ods _all_ close;