Table prompt

[Max-A]

Hello Everyone,

I've been doing some research to find a way to create a automatic test procedure to check if there are double values in a dataset. I think it must be possible to show a prompt on startup to select the dataset that has to be checked. Correct me if i am wrong.

The problems i encouter are the following:

- I cannot find a way to specify a specific dataset with a prompt. Only a file or directory. But the file is not on the etl server, and so i cannot use it.

- I want to create a testprogram in which a lot of values can be changed. For example:

 What variable must be unique? (test 1)

 What variable cannot be empty? (test 2)

I am quite new to the sas software, so i hope someone can help me.

Looking forward to the reactions!

[Kurt_Bremser]

You can define a user prompt as selection from a dynamic list, where the values are taken from a dataset.

• create an empty table that will hold the dataset names
• register it in metadata
• use it as source for the prompt

For the finding of double values, proc freq could be very helpful.

[Max-A]

Thanks for your reply!

The problem that i am dealing with is that there are probably over thousand datasets, and every dataset has its own user permissions.

I was hoping that it was possible to search for the specific location of the dataset that i want to check.

The selected dataset would than be the first step of the process flow. After that it must be the source of the list data/query builder or one of the other data tasks.

Hope you have an idea to tackle this problem.

[Kurt_Bremser]

What you describe now is functionality that's already there in Enterprise Guide or SAS Studio.

[Reeza]

---

@Max-A wrote:

Thanks for your reply!

The problem that i am dealing with is that there are probably over thousand datasets, and every dataset has its own user permissions.

I was hoping that it was possible to search for the specific location of the dataset that i want to check.

The selected dataset would than be the first step of the process flow. After that it must be the source of the list data/query builder or one of the other data tasks.

Hope you have an idea to tackle this problem.

---

Are you saying you can't register the table in the metadata library?

Or you want a simpler way?

Why not make it free text so the user enters the name and add a step to verify its a valid table and if not, then it doesn't run.

EDIT: or expand on @Kurt_Bremser idea and add whatever groups/tags you want to the table to organize the data and then add a cascading prompt so the user can find it using the groups/tags you've set up. 

[Max-A]

I think this is more or less the thing i am looking for. 

So this is what i have done:

1. i made a prompt in which the user can specify the location and name of a sas table

2. i made a program with the prompt to start the project

3. I think the only thing i have to do now is to create a prompt that gives a list of the possible variables of the table that is the result of the prompt in step 1. 

Hope it is possible to create a computed column based on the answer that has been given to a prompt.

What i am wondering is if it  is possible to refresh the values of the prompt every time the first step runs

[Reeza]

---

@Max-A wrote:

I think this is more or less the thing i am looking for. 

So this is what i have done:

1. i made a prompt in which the user can specify the location and name of a sas table

2. i made a program with the prompt to start the project

3. I think the only thing i have to do now is to create a prompt that gives a list of the possible variables of the table that is the result of the prompt in step 1. 

Hope it is possible to create a computed column based on the answer that has been given to a prompt

---

You can create that list relatively easily, use dictionary.vcolumn to pull all the variable names from a table. Probably need to create a view or something that lists it and then set that as the prompt parameter.

[Kurt_Bremser · Accepted solution]

You need to populate a dataset, which is already registered in metadata, with the variable names from dictionary.columns (or sashelp.vcolumn). You can build a dynamic prompt from that.