How to make a fancy PDF/EXCEL output

[PaigeMiller]

But TESTSTED does not have those values.

Are you trying to say you want to assign those values to TESTSTED? And overwrite the values in TESTSTED that were shown there?

Please just type out the new variable(s) added into the existing data set, I don't care whether the SAS code to read the values is correct, I just want to see the full data set with the new information in its proper place, whatever that is. I cannot picture this from your words.

[mmea]

data WORK.TEST;
  infile datalines delimiter=','; 
  input region:12.  teststed:$84. dato:date9. 
        ventetid:12. Metode:$14.
    format dato ddmmyyd8.;
datalines4;
Hovedstaden, Bornholm Covid-19 teststed , 15DEC2020, 0.06, Nære kontakter
Hovedstaden, Hillerød Covid-19 teststed, 15DEC2020, 0.01, Almen podning
Hovedstaden, Københavns lufthavn Covid-19 teststed , 15DEC2020,0.02, Nære kontakter
Hovedstaden, taastrup covid-19 teststed, 16DEC2020,0.7,Almen podning
Midtjylland, Covid nære kontakter,16DEC2020,0.3, Nære kontakter
Midtjylland, Covid testcenter nære kontakter ,16DEC2020,0.0006, Almen podning
Midtjylland, Covid vejby-risskov hallen SSI, 16DEC2020,0.004, Nære kontakter 
Midtjylland, Covid viby hallen jylland SSI,16DEC2020, 0.002, Almen podning
;;;;

I have made a new variable called Metode with the variables Nære kontakter and Almen podning,

Those are the variables are need to divide the teststed in the output i showed. 

If teststed contains something with nære then put in Nære kontakter group. If teststed contains something with almen podning put in almen podning.

Is it possible to put the color line then?

[PaigeMiller]

I think the problem I was having with your descriptions was that you said you wanted two new variables, XXX and YYY, but you have only one new variable. Here is the code

data WORK.TEST;
  infile datalines delimiter=',' truncover; 
  input region $12.  teststed :$55.  dato:date9. 
        ventetid:12. Metode:$14.;
    format dato ddmmyyd8.;
datalines4;
Hovedstaden,Bornholm Covid-19 teststed,15DEC2020,0.06,Nære kontakter
Hovedstaden,Hillerød Covid-19 teststed,15DEC2020, 0.01, Almen podning
Hovedstaden,Københavns lufthavn Covid-19 teststed,15DEC2020,0.02, Nære kontakter
Hovedstaden,taastrup covid-19 teststed,16DEC2020,0.7,Almen podning
Midtjylland,Covid nære kontakter,15DEC2020,0.3, Nære kontakter
Midtjylland,Covid testcenter nære kontakter ,16DEC2020,0.0006, Almen podning
Midtjylland,Covid vejby-risskov hallen SSI,16DEC2020,0.004, Nære kontakter 
Midtjylland,Covid viby hallen jylland SSI,16DEC2020, 0.002, Almen podning
;;;;

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;

	proc report data=test;
	    columns region metode teststed dato,ventetid;
	    define region/group;
	    define teststed/group;
	    define metode/group;
	    define dato/across;
	    define ventetid/sum format=pctf5. ' ';
	    compute ventetid;
	          %do i=1 %to &ncols;
	               call define("_c%eval(&i+3)_",'style','style={background=colorf.}');
	          %end;
	      endcompute;
	run;
%mend;

%many_col

[mmea]

data WORK.TEST;
  infile datalines delimiter=','; 
  input region:12.  teststed:$84. dato:date9. 
        ventetid:12. 
    format dato ddmmyyd8.;
datalines4;
Hovedstaden, Bornholm Covid-19 teststed , 15DEC2020, 0.6
Hovedstaden, Hillerød Covid-19 teststed, 15DEC2020, 0.01
Hovedstaden, Københavns lufthavn Covid-19 teststed , 15DEC2020,,0.02
Hovedstaden, taastrup covid-19 teststed, 16DEC2020,0.7
Midtjylland, Covid nære kontakter,16DEC2020,0.3
Midtjylland, Covid testcenter nære kontakter ,16DEC2020,0.0006
Midtjylland, Covid vejby-risskov hallen SSI, 16DEC2020,0.004 
Midtjylland, Covid viby hallen jylland SSI,16DEC2020, 0.002
;;;;

I have made a data set matchin the wished out put.

I would like also to make the ventetid as percentage so multipli with 100, and then add the color line. If over 10 % then coloar green and the rest red

[mmea]

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

Is it possible to devide this report further, so in my excel ouput I will have several sheets based on region. So each sheet will only be one region?

[mmea]

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 

[Kurt_Bremser]

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.