topic Re: Limiting allowed values in Excel using proc report in ODS and Base Reporting

Limiting allowed values in Excel using proc report

klmd189 — Thu, 18 Jul 2019 08:54:52 GMT

Dear all,

I wonder if a possibility exists to limit possible values in desired column after producing Excel file using proc report.

I would like to code this option in SAS instead of setting this option in Excel by Data>Data Tools>Data Validation>Allow>List of values.

I just want to have one column with possible values limited to Y/N. Input data has this column empty but when it comes to editing this worksheet I want only Y/N values to be there.

Re: Limiting allowed values in Excel using proc report

ballardw — Thu, 18 Jul 2019 15:11:29 GMT

An appropriate format used for the values in Proc Report should work. But without data and report code it is hard to tell exactly what might be needed.

I am a bit unclear on how you expect "Input data has this column empty " to assign Y and N to specific cells though.

If you can provide an example of the data used for proc report in the form of a data step so that we can recreate data and test code, indicate which values need to appear as Y/N, plus your proc report code we can provide a more targeted response. Post the data step code and the report code into a code box on the forum using the {I} or "running man" icon.

Re: Limiting allowed values in Excel using proc report

klmd189 — Mon, 22 Jul 2019 07:38:13 GMT

Thank your for your response. The text you quoted indicates that input file will have the column with missing value for all observations and the task is to make excel file from the input data so it is editable in MS Office tools. When someone wants to put a value into this column with empty values then I want him to have the possibility only to put "Y" or "N" value.

In code below I name this variable as yn.

This is an example of input data.

data test;
attrib subject label="Enrolment Code" length = $5
       yn label="Yes or no" length = $1
       ;
subject = "E1234";
yn = "";
output;
subject = "E1235";
yn = "";
output;
run;

Below I attach the desired output with data validation function.

Re: Limiting allowed values in Excel using proc report

jdveerapaneni — Wed, 14 Aug 2024 14:00:03 GMT

Hi, were you able to get any solution for this questions?

I am working on same requirement and unable to find if i generate Drop down list in excel automatically using SAS EG (proc report & ODS).