How to make a fancy PDF/EXCEL output

[mmea]

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

[mmea]

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

[PaigeMiller]

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.

[mmea]

This is the current table:

So basically i want to sa in the proc report: if teststed is equal to a certain text then make a new variable A. if teststed is equal to another certain text then put it under new variable B

[PaigeMiller]

I asked for data that would allow me to construct this table.

Also, you are not creating new VARIABLEs in the output shown, you are just placing text on the output, so I don't really know what you mean.

[mmea]

data WORK.TEST;
  infile datalines delimiter=','; 
  input regionsnummer:12.  teststed:$84.  dato:date9. 
        ventetid:12. ledigtid:$4.;
    format dato ddmmyyd8.;
datalines4;
1802,Tescenter NA,01DEC2020, 0.6, >10%
1802, Testcenter BA, 01DEC2020,0.01 ,<10%
1803, Testcenter NA, 02DEC2020, 0.02,<10%
1803, Testcenter BA, 03DEC2020,0.7 ,>10%
1803, Testcenter NA, 04DEC2020,0.3 ,>10%
1804, Testcenter NA,05DEC2020,0.0006 ,<10%
1804, Testcenter BA, 01DEC2020,0.004 ,<10%
1804, TestcenterBA,02DEC2020, 0.002,<10%
;;;;

This is the data.

Basically I want for each region two groups based on teststed. So if teststed  contains NA then make a new variable called  "XXX". If tested contains BA then make a new variable calle "YYY" . So the output will be the same as before, but under teststed there will be two groups "XXX" and "YYY"

[PaigeMiller]

---

@mmea wrote:

data WORK.TEST;
  infile datalines delimiter=','; 
  input regionsnummer:12.  teststed:$84.  dato:date9. 
        ventetid:12. ledigtid:$4.;
    format dato ddmmyyd8.;
datalines4;
1802,Tescenter NA,01DEC2020, 0.6, >10%
1802, Testcenter BA, 01DEC2020,0.01 ,<10%
1803, Testcenter NA, 02DEC2020, 0.02,<10%
1803, Testcenter BA, 03DEC2020,0.7 ,>10%
1803, Testcenter NA, 04DEC2020,0.3 ,>10%
1804, Testcenter NA,05DEC2020,0.0006 ,<10%
1804, Testcenter BA, 01DEC2020,0.004 ,<10%
1804, TestcenterBA,02DEC2020, 0.002,<10%
;;;;

This is the data.

---

This is not the data. This is the old data. The new variables don't appear in the data. So I have a simple question about these new variables XXX and YYY. What values will they have?

Basically I want for each region two groups based on teststed. So if teststed  contains NA then make a new variable called  "XXX". If tested contains BA then make a new variable calle "YYY" . So the output will be the same as before, but under teststed there will be two groups "XXX" and "YYY"

Please, you modify the data above such that you have new variables XXX and YYY, because its not clear to me what that means for this data, and show me the data properly modified with variables XXX and YYY.

[mmea]

The new variables will have the same values as the TESTSTED but based on the names - if TESTSTED contains BA in the name then it will be under anew variable called XXX in the report etc.

here is my wished output

As before - divided by region - instead of just teststed, then for each TESTSTED and REGION there will be 2 new variables called "Nære kontakter" and "alm. podning"

[PaigeMiller]

You really need to write words that match the table you show. I do not see XXX in the table you show.

[mmea]

data WORK.TEST;
  infile datalines delimiter=','; 
  input region:12.  teststed:$84. analysebeskrivelse:$564 dato:date9. 
        ventetid:12. 
    format dato ddmmyyd8.;
datalines4;
Hovedstaden, Bornholm Covid-19 teststed NA, XXX,15DEC2020, 0.6
Midtjylland, teststed BA, 15DEC2020,XXX,0.01
Hovedstaden, teststed BA, 15DEC2020, YYY,0.02
Midtjylland, teststed NA, YYY,16DEC2020,0.7
Midtjylland, Testcenter BA, XXX,16DEC2020,0.3
Hovedstaden, COVID NA ,YYY,16DEC2020,0.0006
Midtjylland, Testcenter BA,YYY,16DEC2020,0.004 
Hovedstaden, Testcenter BA, XXX,16DEC2020, 0.002
;;;;

If TESTED contains BA  then make a variable called "NÆRE KONTAKTER" under TESTSTED in the proc report.

If TESTED contains NA then make a variable called "ALM. PODNING" under TESTSTED in the proc report.

these two variables do not exist in my dataset. But i have seen that you can make new columns in the proc report based of the statement i mentioned before?

The names dont match the example output i gave - it is just an example

so if will look like the recent example ouput that ive posted.

Also if possible I would look to make the VENTETID appear as percentages so multiplied with 100 in the proc report.

Instead of coloring the whole boxes is it possible to onlye color a fraction of the column like the recent output example?

[PaigeMiller]

I continue to ask that you do this work of creating new variables. You still have not told me what values these new variables have. So show me the data with these two new variables.

I will then be happy to help with PROC REPORT.

[mmea]

I thought that it was possible to create new variables within the proc report itself?

[PaigeMiller]

Yes, you can, but I still don't have an answer to what values these variables contain. You haven't given me an answer.

For example, in the DATA step, I could write something like this

if find(teststed,'NA')>0 then XXX= 

but I don't know how to finish the statement, I don't know what value XXX should have.

[mmea]

i understand that. I cant do it like that in the datastep, beacuse I have over 2000 different values under TESTSTED - then it will be to hard for me to call a new variable with the same text from TESTSTED without writing the whole name.

The new variables should contains the values from TESTSTED:)

[mmea]

Lets say i have TESTSTED.

TESTSTED have these values:

NA

BA

NA

BA

NA

BA

I want to make a new variable in proc report that says:

if TESTSTED includes something with NA then put as a undergroup under TESTSTED in proc report with the value NA

and then the same for BA