Need guidance for best way to capture user entered prompts and create table for report.

[ncsthbell]

I am working on reports where the users may enter up to 20+ prompt filters.   What I need to do is create a work table that would included column names for 'Prompt1_ColName',  'Prompt1_Value', 'Prompt2_ColName',  'Prompt2_Value'... and so on.

I am testing the length of the prompts to check if a value has been entered.  I would like to create a work table that contains these values so I can use it for reporting.  Not sure about best way to create the work table.  I have only created tables using select queries.  In looking at using a data step, it looks like tthis is only for creating datasets.

Any direction would be greatly appreciated!!

Thanks 

[daddio42]

Option1: If you are using SAS Enterprise Guide, there is a capability called "Prompt Manager" that provides the capability to display prompts to the user at run time. These prompts are stored as global macro variables which can be used throughout the session.

Option2: Create a confi sas program which is executed 1st using %LET = for each user prompt, thereby storing the prompt values in global macro variables which can be used throughout the session.

Option3: Create a reference sas dataset with prompt names and values and read the values into global macro variables which can be used throughout the session.

[ncsthbell]

Hello ddadio42,

Option1 - I am not using EG, use SAS Base code

Option2 - I am currently using a %Let statement in my code to create a global variable for each prompt.

Option3 - I would prefer not to create a dataset that holds the prompt values as there will be many users of these reports and I don't want to have any conflicts with the dataset.  That is why I want to build a work table.

[Astounding]

Regardless of the method you choose, I would suggest changing the structure.  It will be much more flexible to use 20+ records, each with 3 columns:  prompt_no (1-20), prompt_name, prompt_value.

[ncsthbell]

Astounding, 

What you stated is exactly what I am doing, however, I just can't figure out how to get each row into a work table, not a dataset.

Thanks!

[Astounding]

How is a work table different from a data set?

[ncsthbell]

Doesn't a sas dataset have to have a physical permanent location where a work table is on the sas server?  I have not worked with SAS datasets either, only SAS tables.   So, for that reason I also thought it would be better for me to use a work table.

I want to take this table and use as source into a proc report.

[Astounding · Accepted solution]

OK ... not always ... but I see where you are coming from.  There are permanent SAS data sets and temporary SAS data sets (stored in the WORK area), which use the same structure.  If you wanted to keep the SAS data sets permanently, but distinguish which one belonged to which user, you might name them perm._&sysuserid (assuming that &sysuserid contains legal characters for a data set name).

Moving from macro variables to a data set might require defining a macro, to be able to use a %DO loop.  But it could be done along these lines:

data want;

%do i=1 %to 20;

   col_no = &i;

   col_name = "prompt&i._colname";

   col_value = "prompt&i._value";

   output;

%end;

run;

This code makes the names and values character.  If COL_VALUE is character some of the time, it has to be character all the time.

[data_null__]

What about SYMGET? 

---

@Astounding wrote:

Moving from macro variables to a data set might require defining a macro, to be able to use a %DO loop.  But it could be done along these lines:

[Astounding]

Good idea.  SYMGET would work nicely here.  You could use a DATA step DO loop (containing an OUTPUT statement) instead of a macro loop.

Also note, you will need to pay attention to the lengths of the variables.  SYMGET handles that automatically, creating character variables that are $200 long.  My code uses the length of the first prompt entered, which may be too short for later prompts.