topic Creating Table with Prompt in SAS Enterprise Guide

Creating Table with Prompt

VIU — Wed, 24 Jan 2018 11:11:31 GMT

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

Re: Creating Table with Prompt

FredrikE — Wed, 24 Jan 2018 13:22:02 GMT

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

Re: Creating Table with Prompt

VIU — Wed, 24 Jan 2018 14:29:56 GMT

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?

Re: Creating Table with Prompt

FredrikE — Wed, 24 Jan 2018 14:46:53 GMT

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