How to make a fancy PDF/EXCEL output

[mmea]

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?

[PaigeMiller]

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.

[mmea]

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;

[mmea]

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;

[mmea]

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?

[PaigeMiller]

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.

[mmea]

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?

[PaigeMiller]

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.

[mmea]

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;

[PaigeMiller]

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.

[mmea]

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

[PaigeMiller]

---

@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?

[mmea]

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.

[PaigeMiller]

%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

[mmea]

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?