DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

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

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 


Accepted Solutions
Solution
‎04-11-2016 09:27 AM
Super User
Posts: 5,099

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

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.

View solution in original post


All Replies
Learner
Posts: 1

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

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.

 

 

Frequent Contributor
Posts: 82

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

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.

Super User
Posts: 5,099

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

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.

Frequent Contributor
Posts: 82

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

[ Edited ]

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!

Super User
Posts: 5,099

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

How is a work table different from a data set?

Frequent Contributor
Posts: 82

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

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.

Solution
‎04-11-2016 09:27 AM
Super User
Posts: 5,099

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

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.

Respected Advisor
Posts: 3,777

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

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:

 

 

Super User
Posts: 5,099

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 389 views
  • 0 likes
  • 4 in conversation