Desktop productivity for business analysts and programmers

Creating Table with Prompt

Reply
New Contributor VIU
New Contributor
Posts: 2

Creating Table with Prompt

I created an empty programm in SAS EG, associated a prompt with a Datasourceelement (there are some tables where i need just some columns from, that selection can change so i need it that way).

When i start the programm the prompt gets displayed and i can select the columns from the different tables and proceed(but nothing happens except i get the log), but how i can create a table with the selected columns and data?


I checked some macro_variables... i get:  %LET Prompt_count=Number of selected columns, the name of the columns %LET Prompt = xyz, Prompt1 = zxy, some %SYMDEL and so on...

 

Thxs and i´m new in SAS

Super Contributor
Posts: 344

Re: Creating Table with Prompt

I'd say this is quite a complex task to solve.

 

You need to create a program and add all tables in some kind of a join and then select column to use depending on your prompt selections.

To get all columns you need to loop through all prompt values with a macro loop.

 

I can't see how to solve this in an easy way.

 

You also want the data from the tables...so...the following example (dummy) code might give you some input:

 

1. Create a keep statement that have all columns from the prompts:

%macro create_keep;

  %let keep_stmt = ;

  %do i = 1 %to &prompt_count;

      %let keep_stmt =  &keep_stmt &prompt_value&i;

  %end;

%mend;

%create_keep;

 

2. Create a sql statement or data step to select data and chose columns;

data one(keep=&keep_stmt);

 merge table1 table2 table3;

 by key;

run;

 

Why use prompts instead of standard transformations and queries?

 

//Fredrik

 

New Contributor VIU
New Contributor
Posts: 2

Re: Creating Table with Prompt

Hi FredrikE & thxs,

i want to build a pre-defined table with some attributes which i get from some tables and add others if i need them..i thought thats the easiest way through prompts, but it doesnt seem so...is there an other way except putting all tables to EG and standard querying it?

 

Super Contributor
Posts: 344

Re: Creating Table with Prompt

I would create a EG project that creates your predefined table by using the query task and then modify the query depending on which columns you want.

 

You can easily copy/paste the query if you want multiple tables with different columns.

 

I think it is possible to achieve what you want by coding, but I wonder if it is worth the time/money to do that instead of using the standard tasks.

 

//Fredrik

Ask a Question
Discussion stats
  • 3 replies
  • 128 views
  • 0 likes
  • 2 in conversation