topic Re: How to make a fancy PDF/EXCEL output in New SAS User

How to make a fancy PDF/EXCEL output

mmea — Tue, 29 Dec 2020 16:24:40 GMT

Hi i have a data set looking like this:

I made a proc report with the desired variables from this data set looking like this:

The code do not giveme the out put that I want. I wish to make something like this:

Region X	18/12	19/12	20/12	21/12	21/12
Testcenter X	green	red
Testcenter Y	green	red	green	green	green
Testcenter Z

A table for every region from the variable "Regionsnummer" and under that

will be testcenter X Y Z which is based on the variable "Teststed". the dates are based on the variable "Dato" and I want the format with only day and month. The values in the boxes represent the "Ledigtid" so ">10%" is green and "<10%" is red. The boxes should be colored here.

is that possible. Ive tried everything..

Is it possible to output this in Excel?

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sat, 19 Dec 2020 12:36:45 GMT

Yes, all this is possible, but without having (a portion of) your data available, its difficult for me to write such code.

Please provide (a portion of) the data according to these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ (Do not provide data as screen captures or attachments)

You would be wise to leave your dates as true SAS dates, and display dates as day-month-year instead of leaving out the year. If that's absolutely critical that you leave out the year in the report, then you are probably still better off using true SAS dates, and creating a custom format to display them as day-month.

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 12:51:34 GMT

This i my data:

proc import datafile = "F:\Grupper\DIAS\KID\devel\MMEA\SSI kapacitet Samfundsspor - 2020-12-15.csv" dbms=csv
out= work.vente replace;
delimiter = ";";
guessingrows=3000;
run;

proc sql;
create table ventetider as
select distinct regionsnummer, teststed, dato, analysebeskrivelse, sum(kapacitet)as kapacitet, sum(booket_kapacitet) as booket_kapacitet
from ventetider
group by dato, teststed, analysebeskrivelse
order by dato, teststed;
quit;

then i make a new column to get the "ledigtid"

data pdf1;
set pdf1;
ventetid = (kapacitet - booket_kapacitet)/kapacitet;
if ventetid >= 0.1 then farve = 1;
if ventetid < 0.1 then farve = 2;
if ventetid = . then farve = 3;
if ventetid >= 0.1 then Ledigtid = ">10%";
if ventetid < 0.1 then Ledigtid = "<10%";
run;

proc sort data= pdf1;
by dato;
run;

my data is big but here is some data lines, just one line:

data WORK.TEST;
  infile datalines dsd truncover;
  input Make:BEST12. regionsnummer:$84. teststed:DDMMYY10.. dato:$564. analysebeskrivelse:BEST12.
        ventetid:$4. ledigtid;
datalines4;
1801,Tescenter X,"12/1/2020", 0.6, ">10%";
proc print;
run;

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 12:54:21 GMT

Sorry I made the data lines wrong

data WORK.TEST;
  infile datalines dsd truncover;
  input regionsnummer:BEST12.  teststed:$84.  dato: DDMMYY10. analysebeskrivelse:$564. 
        ventetid:BEST12.  ledigtid:$4.;
datalines4;
1801,Tescenter X,"12/1/2020", 0.6, ">10%";
proc print;
run;

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sat, 19 Dec 2020 12:54:33 GMT

I can't program a report from data that has only one record. Please make a realistic subset of your data that would be good to create a report from.

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 12:57:50 GMT

data WORK.TEST;
  infile datalines dsd truncover;
  input regionsnummer:BEST12.  teststed:$84.  dato: DDMMYY10. analysebeskrivelse:$564. 
        ventetid:BEST12.  ledigtid:$4.;
datalines4;
1801,Tescenter X,"12/01/2020", 0.6, ">10%"
1802, Testcenter Y, "12/02/2020", 0.01, "<10%"
1803, Testcenter Z "12/03/2020", 0.02, "<10%"
1801, Testcenter W "12/04/2020", 0.1, ">10%"
1802, Testcenter Y-N, "12/05/2020", 0.2, ">10%"
1802, Testcenter YX "12/13/2020", 0.01, "<10%"
1804, Testcenter YB, "12/15/2020", 0.01, "<10%"
1803, Testcenter Y,V "12/20/2020", 0.01, "<10%";
proc print;
run;

is that fine?

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 12:58:14 GMT

data WORK.TEST;
  infile datalines dsd truncover;
  input regionsnummer:BEST12.  teststed:$84.  dato: DDMMYY10. analysebeskrivelse:$564. 
        ventetid:BEST12.  ledigtid:$4.;
datalines4;
1801,Tescenter X,"12/01/2020", 0.6, ">10%"
1802, Testcenter Y, "12/02/2020", 0.01, "<10%"
1803, Testcenter Z "12/03/2020", 0.02, "<10%"
1801, Testcenter W "12/04/2020", 0.1, ">10%"
1802, Testcenter Y-N, "12/05/2020", 0.2, ">10%"
1802, Testcenter YX "12/13/2020", 0.01, "<10%"
1804, Testcenter YB, "12/15/2020", 0.01, "<10%"
1803, Testcenter Y,V "12/20/2020", 0.01, "<10%";
proc print;
run;

is that fine?

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sat, 19 Dec 2020 13:38:54 GMT

It's hard to know if this is fine, the code doesn't run, and I think you are missing a few commas and/or I think you have commas in the wrong places.

So please clean this up. Make sure the code runs and gives the desired data set TEST.

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 13:57:05 GMT

data WORK.TEST;
  infile datalines delimiter=','; 
  input regionsnummer:12.  teststed:$84.  dato:date9. analysebeskrivelse:$564. 
        ventetid:12. ledigtid:$4.;
datalines4;
1801,TescenterX,01DEC2020, Nære kontakter, 0.6, >10%
1802, TestcenterY, 01DEC2020, Nære kontakter,0.01 ,<10%
1803, TestcenterZ, 02DEC2020, Åbne testtilbud, 0.02,<10%
1801, TestcenterW, 03DEC2020,Åbne testtilbud ,0.7 ,>10%
1802, TestcenterYN, 04DEC2020,Åbne testtilbud,0.3 ,>10%
1802, TestcenterYX,05DEC2020,Nære kontakter,0.0006 ,<10%
1804, TestcenterYB, 01DEC2020, Åbne testtilbud,0.004 ,<10%
1803, TestcenterYV,02DEC2020, Åbne testtilbud, 0.002,<10%
;;;;

proc print;
run;

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sat, 19 Dec 2020 15:52:03 GMT

I still made some minor changes to your WORK.TEST code

data WORK.TEST;
  infile datalines delimiter=','; 
  input regionsnummer:12.  teststed:$84.  dato:date9. analysebeskrivelse:$564. 
        ventetid:12. ledigtid:$4.;
    format dato ddmmyyd8.;
datalines4;
1801,TescenterX,01DEC2020, Nære kontakter, 0.6, >10%
1802, TestcenterY, 01DEC2020, Nære kontakter,0.01 ,<10%
1803, TestcenterZ, 02DEC2020, Åbne testtilbud, 0.02,<10%
1801, TestcenterW, 03DEC2020,Åbne testtilbud ,0.7 ,>10%
1802, TestcenterYN, 04DEC2020,Åbne testtilbud,0.3 ,>10%
1802, TestcenterYX,05DEC2020,Nære kontakter,0.0006 ,<10%
1804, TestcenterYB, 01DEC2020, Åbne testtilbud,0.004 ,<10%
1803, TestcenterYV,02DEC2020, Åbne testtilbud, 0.002,<10%
;;;;

proc format;
    value pctf .=' ' 0-<0.1='<10%' 0.1-high='>=10%';
    value colorf .=white 0-<0.1=lightmoderategreen 0.1-high=lightmoderatered;
run;
proc report data=test;
    columns regionsnummer teststed dato,ventetid;
    define regionsnummer/group;
    define teststed/group;
    define dato/across;
    define ventetid/sum format=pctf5.;
    compute ventetid;
          call define("_c3_",'style','style={background=colorf.}');
          call define("_c4_",'style','style={background=colorf.}');
          call define("_c5_",'style','style={background=colorf.}');
          call define("_c6_",'style','style={background=colorf.}');
          call define("_c7_",'style','style={background=colorf.}');
      endcompute;
run;

If you have many different values of VENTETID, and it is impractical to write so many lines under COMPUTE VENTETID, then a macro can do this as well.

Re: How to make a fancy PDF/EXCEL output

Kurt_Bremser — Sat, 19 Dec 2020 15:59:57 GMT

You may need to create a format for your dates, so you can use dato as an ACROSS variable with PRELOADFMT (to get the correct order, and have all days in the range in the report even if there's no data for one.

If all datest are covered in the correct order, you can omit this and use a picture format that contains only day and month.

regionsnummer and teststed will be GROUP variables; or you use regionsnummer as a BY variable.

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 16:45:38 GMT

Thank you!. It looks very good with the data I made for the example.

In my real data i have over 5000 observations. How can a macro be used in this case?

I have no experience in SAS - so its hard for me to find this information

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sat, 19 Dec 2020 21:40:14 GMT

@mmea wrote:

Thank you!. It looks very good with the data I made for the example.

In my real data i have over 5000 observations. How can a macro be used in this case?

I have no experience in SAS - so its hard for me to find this information

Macro would be used for many columns, which come from many dates. Is that the situation you have? Number of observations is not relevant. How many dates are there? (Or don't you know until you get the data?)

Also, can there be more than one observation for a specific combination of date/region/testcenter? If so, should the output have multiple rows for that combination of date/region/testcenter?

Re: How to make a fancy PDF/EXCEL output

mmea — Sat, 19 Dec 2020 21:47:14 GMT

There Will Always approximately be around 2 weeks of dates. I get new data everyday so there will always be new dates from the current date that I get the data.

The output should give all combinations. There can be observations with same teststed but different values in other columns.

Hope that can help.

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sat, 19 Dec 2020 22:29:50 GMT

%macro many_col;
    proc sql noprint;
        select distinct dato into :ncols from test;
    quit;
proc report data=test;
    columns regionsnummer teststed dato,ventetid;
    define regionsnummer/group;
    define teststed/group;
    define dato/across;
    define ventetid/sum format=pctf5.;
    compute ventetid;
          %do i=1 %to &ncols;
               call define("_c%eval(&i+2)_",'style','style={background=colorf.}');
          %end;
      endcompute;
run;
%mend;

%many_col

Re: How to make a fancy PDF/EXCEL output

mmea — Sun, 20 Dec 2020 17:56:34 GMT

Thank you for that.

I had a variable called Analysebeskrivelse in the datalines.

But I cannot see it in use in the proc report code?

Re: How to make a fancy PDF/EXCEL output

mmea — Sun, 20 Dec 2020 18:02:27 GMT

I ran this code with my real dataset

It works an looks good. But the colors do not show in the fields for ventetid 🙂

What did i do wrong here?

%macro many_col;
    proc sql noprint;
        select distinct dato into :ncols from pdf1;
    quit;

proc format;
    value pctf .=' ' low-<0.1='<10%' 0.1-high='>10%';
    value colorf .=white 0-<0.1='Firebrick' 0.1-high='Steel';
run;
proc report data=pdf1;
    columns region teststed dato,ventetid;
    define region/group;
    define teststed/group;
    define dato/across;
    define ventetid/sum format=pctf5.;
    compute ventetid;
          %do i=1 %to &ncols;
               call define("_c%eval(&i+2)_",'style','style={background=colorf.}');
          %end;
      endcompute;
run;
%mend;

%many_col

Re: How to make a fancy PDF/EXCEL output

mmea — Tue, 29 Dec 2020 16:25:25 GMT

Alsothe ouput shows me the "ventetid " label under the dates. Can i remove that? And why doesnt it color the fields? Also a question, as i am very new in SAS: Can I divide "tested" further i specific names, so that "teststed" will have to different categories:

%macro many_col;
    proc sql noprint;
        select distinct dato into :ncols from pdf1;
    quit;

proc format;
    value pctf .=' ' low-<0.1='<10%' 0.1-high='>10%';
    value colorf .=white low-<0.1=lightmoderatered 0.1-high=Steel;
run;
proc report data=pdf1;
    columns region teststed dato,ventetid;
    define region/group;
    define teststed/group;
    define dato/across format=ddmmyy10.;
    define ventetid/sum format=pctf5.;
    compute ventetid;
          %do i=1 %to &ncols;
               call define("_c%eval(&i+2)_",'style','style={background=colorf.}');
          %end;
      endcompute;
run;
%mend;

%many_col

Re: How to make a fancy PDF/EXCEL output

mmea — Sun, 20 Dec 2020 19:39:38 GMT

Hi.

I have the following code for a proc report.

I have stated that I want some colors, but they do not show in the expected output.

Also I have shown the ouput below - I want to remove the row with the "ventetid" labels - is that possible?

%macro many_col;
    proc sql noprint;
        select distinct dato into :ncols from pdf1;
    quit;
proc format;
    value pctf .=' ' low-<0.1='<10%' 0.1-high='>10%';
    value colorf .=white low-<0.1=lightmoderatered 0.1-high=Steel;
run;
proc report data=pdf1;
    columns region teststed dato, ventetid;
    define region/group;
    define teststed/group;
    define dato/across order=internal format= ddmmyyd10.;
    define ventetid/sum format=pctf5.;
    compute ventetid;
          %do i=1 %to &ncols;
               call define("_c%eval(&i+2)_",'style','style={background=colorf.}');
          %end;
      endcompute;
	  
run;
%mend;

%many_col

Also is it possible in the proc report code to divide the variable "teststed" up in two other groups that will be based on the observations under "teststed" - so e.g. if "teststed" includes something with XXXX then put it in under a new label called "EXAMPLE" etc. So basically under each "region" i want two groups of variables with matching observations.

hope it makes sense

Re: How to make a fancy PDF/EXCEL output

PaigeMiller — Sun, 20 Dec 2020 19:43:47 GMT

My mistake in PROC SQL. This works:

data WORK.TEST;
  infile datalines delimiter=','; 
  input regionsnummer:12.  teststed:$84.  dato:date9. analysebeskrivelse:$564. 
        ventetid:12. ledigtid:$4.;
    format dato ddmmyyd8.;
datalines4;
1801,TescenterX,01DEC2020, Nære kontakter, 0.6, >10%
1802, TestcenterY, 01DEC2020, Nære kontakter,0.01 ,<10%
1803, TestcenterZ, 02DEC2020, Åbne testtilbud, 0.02,<10%
1801, TestcenterW, 03DEC2020,Åbne testtilbud ,0.7 ,>10%
1802, TestcenterYN, 04DEC2020,Åbne testtilbud,0.3 ,>10%
1802, TestcenterYX,05DEC2020,Nære kontakter,0.0006 ,<10%
1804, TestcenterYB, 01DEC2020, Åbne testtilbud,0.004 ,<10%
1803, TestcenterYV,02DEC2020, Åbne testtilbud, 0.002,<10%
;;;;

options mprint;
proc format;
    value pctf .=' ' low-<0.1='<10%' 0.1-high='>10%';
    value colorf .=white low-<0.1=lightmoderatered 0.1-high=lightmoderategreen;
run;

%macro many_col;
    proc sql noprint;
        select count(distinct dato) into :ncols from test;
    quit;
    %put &=ncols;
	proc report data=test;
	    columns regionsnummer teststed dato,ventetid;
	    define regionsnummer/group;
	    define teststed/group;
	    define dato/across;
	    define ventetid/sum format=pctf5. ' ';
	    compute ventetid;
	          %do i=1 %to &ncols;
	               call define("_c%eval(&i+2)_",'style','style={background=colorf.}');
	          %end;
	      endcompute;
	run;
%mend;

%many_col

Also is it possible in the proc report code to divide the variable "teststed" up in two other groups that will be based on the observations under "teststed" - so e.g. if "teststed" includes something with XXXX then put it in under a new label called "EXAMPLE" etc. So basically under each "region" i want two groups of variables with matching observations.

hope it makes sense

No, it does not make sense. Show me an actual data set where you have this, and what the output table should look like.