topic Re: How to Add a Credential to a Specific Cell in Excel Using ODS EXCEL in SAS in SAS Enterprise Guide

How to Add a Credential to a Specific Cell in Excel Using ODS EXCEL in SAS

Daily1 — Fri, 05 Jul 2024 06:30:57 GMT

Hello everyone,

I am working on exporting data to an Excel file using the ODS EXCEL feature in SAS. Along with the data, I need to add a credential (e.g., a code) to a specific cell, say M1.

Here is a sample code snippet that I am using:

/* Sample data */
data sample_data;
    input Name $ Age Gender $ Salary;
    datalines;
    John 30 M 50000
    Jane 25 F 55000
    Sam 28 M 48000
    Lisa 35 F 62000
    ;
run;

/* Export data to Excel using ODS EXCEL */
ods excel file="sample_data.xlsx" options(protect_worksheet='yes');

proc report data=sample_data;
quit;

ods excel options(sheet_interval="none" start_at="M1");

proc odstext;
  p "78YKJXX22";
run;

ods excel close;

I want like

Re: How to Add a Credential to a Specific Cell in Excel Using ODS EXCEL in SAS

Ksharp — Fri, 05 Jul 2024 08:44:51 GMT

/* Sample data */
data sample_data;
infile datalines truncover;
    input Name $ Age Gender $ Salary  (a1-a8 M) ($);
label  M='78YKJXX22'
       a1='*' a2='*' a3='*' a4='*' a5='*' a6='*' a7='*' a8='*';
    datalines;
    John 30 M 50000
    Jane 25 F 55000
    Sam 28 M 48000
    Lisa 35 F 62000
    ;
run;


%let excel= c:\temp\sample_data.xlsx ;




ods _all_ close;
/* Export data to Excel using ODS EXCEL */
ods excel file="&excel." options(protect_worksheet='yes' sheet_name='Sheet1') ;
proc report data=sample_data nowd split='*';
define a1-a8/display style={background=cxFAFBFE foreground=cxFAFBFE bordercolor=cxFAFBFE};
define M/display style={background=cxFAFBFE foreground=black bordercolor=cxFAFBFE};
run;;

ods excel close;

Re: How to Add a Credential to a Specific Cell in Excel Using ODS EXCEL in SAS

Daily1 — Wed, 10 Jul 2024 11:35:36 GMT

I am currently working with SAS and I need to generate an Excel file with a specific sheet that is protected by a password. I know how to use ODS to create Excel files, but I’m unsure how to set a password to protect a sheet within the file. Could someone provide an example of how to do this?

Re: How to Add a Credential to a Specific Cell in Excel Using ODS EXCEL in SAS

PaigeMiller — Wed, 10 Jul 2024 11:38:12 GMT

@Daily1 wrote:

I am currently working with SAS and I need to generate an Excel file with a specific sheet that is protected by a password. I know how to use ODS to create Excel files, but I’m unsure how to set a password to protect a sheet within the file. Could someone provide an example of how to do this?

Please start a new thread for this.

Re: How to Add a Credential to a Specific Cell in Excel Using ODS EXCEL in SAS

Ksharp — Thu, 11 Jul 2024 00:01:54 GMT

Nope.
If I was right, SAS is unable to set a password on a excel file, you need resort to VBA .
or maybe @Cynthina_sas knew something I don't know.