topic Re: How to give bi-colouring for the output in the ODS excel procedure in SAS Programming

How to give bi-colouring for the output in the ODS excel procedure

sah_biostat — Tue, 28 Feb 2023 14:58:33 GMT

Hello Team,

I have a sas dataset which contains all the ADaM datasets atrributes.I would like to export this dataset to excel.Is there any option to get the output with specific colours to differentiate the different datasets?

If you see above screenshot my desired output should show all the adae information into one colour and all the admh into one colour.Are there any options ?

ods excel file="xxxx.xlsx";
proc print data=final noobs;
run;
ods excel close;

Please kindly help.

Re: How to give bi-colouring for the output in the ODS excel procedure

PaigeMiller — Tue, 28 Feb 2023 15:11:34 GMT

proc report data=yourdataset;
    columns variable1 variable2;
    compute variable1;
        if variable1='ADAE' then call define(_row_,'style','style={background=lightyellow}');
        else if variable1='ADMH' then call define(_row_,'style','style={background=lightmoderategreen}');
    endcompute;
run;

From now on, do NOT provide data as screen captures. We cannot program from screen captures. We need data as working SAS data step code (instructions), not screen captures or file attachments. In addition, in your screen capture, you didn't even show us the variable names, hence I use VARIABLE1 and VARIABLE2 as variable names, which you can fix.

Re: How to give bi-colouring for the output in the ODS excel procedure

sah_biostat — Tue, 28 Feb 2023 15:18:16 GMT

Hello @PaigeMiller ,

Thanks for the quick response.

Actually, I have datasets, variable, length, label as the variable names. I have many datasets in the datasets column .I cannot give style option manually. So, I am looking for any default options which i can put inside the macro.

Re: How to give bi-colouring for the output in the ODS excel procedure

PaigeMiller — Tue, 28 Feb 2023 16:15:32 GMT

@sah_biostat wrote:

Actually, I have datasets, variable, length, label as the variable names. I have many datasets in the datasets column .I cannot give style option manually. So, I am looking for any default options which i can put inside the macro.

How many different datasets are there? How many different colors do you need? What do you mean by "bi-coloring"? Can you provide example data (in the format I mentioned above)?

Also, you did not mention a macro in your original message. What do you want a macro to do?

Re: How to give bi-colouring for the output in the ODS excel procedure

sah_biostat — Tue, 28 Feb 2023 17:34:34 GMT

@PaigeMiller

I have 15 +datasets in the dataset column.

Bi -colouring means just two colours simultaneously to differentiate the datasets visually. For instance, the first adam dataset is in grey, the second in white and repeating. Here, macro means this is for one study and I would like to apply this for every study so,we cannot fix dataset number(every study varies).For instance,leave macro topic. If we get solution for one study also fine.Looking for the options to get the output in desired way.

Sorry for attaching the screenshot I am unable to explain you clearly without the image.

Re: How to give bi-colouring for the output in the ODS excel procedure

PaigeMiller — Tue, 28 Feb 2023 18:26:37 GMT

I realize that you are new here, but I have previously said I cannot work from data in screen captures. Data must be in the form of working SAS data step code, which you can type in yourself or follow the instructions I gave. Data cannot be in the form of file attachments or screen captures.

This is untested code, as I don't have data to test it on.

proc sort data=have;
    by impacted_adam;
run;
data temporary;
    set have;
    by impacted_adam;
    if first.impacted_adam then group+1;
run;
proc report data=temporary;
    columns group impacted_adam variable ... ;
    define group/noprint;
    compute variable1;
        if mod(group,2)=1 then call define(_row_,'style','style={background=lightgrey}');
        else call define(_row_,'style','style={background=lightmoderateyellow}');
    endcompute;
run;

Re: How to give bi-colouring for the output in the ODS excel procedure

Cynthia_sas — Tue, 28 Feb 2023 18:16:18 GMT

Hi:

One way around having to specify values in the COMPUTE block is to use a user-defined format, as shown here:

I based the format on the first character of the name field in the SASHELP.CLASS file. The variable I created was called ALFA. Your format would have the dataset names and the colors you choose. I picked 4 colors. You could pick 2 different colors. In the PROC REPORT code, the ALFA column is a NOPRINT column, so it is available to be used, but does not display on the report. This kind of trafficlighting should also work in ODS EXCEL. Without any example data, it is hard to do more than make general suggestions.

Cynthia