topic Re: SAS EG prompt - how to use in query builder? in SAS Enterprise Guide

SAS EG prompt - how to use in query builder?

Wouter — Mon, 30 Apr 2018 15:26:09 GMT

All, I maybe do have a simple question... In EG, I've created a prompt ("rep_date") in which a user can enter some number (date). This date I want to use in a query builder, as part of the name of a new SAS table (see picture in the doc file).

However, the table created doesn't contain the text as entered by the user, but the name of the macro (so the output table is called WORK.Test_&rep_date in stead of something like WORK.Test_31JAN2018).

Is there a solution for this, or is it not possible to use a macro var this way? A text based prompt doesn't work either...

I'm on SAS EG 7.12.

Thanks for the response!

Re: SAS EG prompt - how to use in query builder?

Reeza — Mon, 30 Apr 2018 15:28:43 GMT

You can definitely use it in the manner. Can you detail how you created it?

What's the macro variable name and what did you get as output?

Many people can't/won't download attachments and you can include screenshots by using the photos button in the editor.

@Wouter wrote:

All, I maybe do have a simple question... In EG, I've created a prompt ("rep_date") in which a user can enter some number (date). This date I want to use in a query builder, as part of the name of a new SAS table (see picture in the doc file).

However, the table created doesn't contain the text as entered by the user, but the name of the macro (so the output table is called WORK.Test_&rep_date in stead of something like WORK.Test_31JAN2018).

Is there a solution for this, or is it not possible to use a macro var this way? A text based prompt doesn't work either...

I'm on SAS EG 7.12.

Thanks for the response!

Re: SAS EG prompt - how to use in query builder?

Wouter — Mon, 30 Apr 2018 16:09:03 GMT

Hi, thank you for your answer. I've created the 'prompt variable' at the beginning of the project. When I want it to use later in a query builder (see also the picture below): the output name is: WORK.HHI_Banks_&rep_date, and the output table is... indeed called WORK.HHI_Banks_&rep_date...

Prompt

First query in project where Rep_date is created by prompt

The query builder

Re: SAS EG prompt - how to use in query builder?

Reeza — Mon, 30 Apr 2018 16:23:15 GMT

Dumb question, have you connected the prompt to the query so that it's selected or defined before the run?

@Wouter wrote:

Hi, thank you for your answer. I've created the 'prompt variable' at the beginning of the project. When I want it to use later in a query builder (see also the picture below): the output name is: WORK.HHI_Banks_&rep_date, and the output table is... indeed called WORK.HHI_Banks_&rep_date...

Prompt

First query in project where Rep_date is created by prompt

The query builder

Re: SAS EG prompt - how to use in query builder?

Wouter — Tue, 01 May 2018 07:30:20 GMT

Yes, at 2 places now; one at the start of the project (ticked: "use prompt value throughout project) and now one at the query builder in question itself. Same outcome.

Part of the log:

NOTE: Compressing data set WORK.test_&REP_DATE'n increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: Table WORK.'test_&REP_DATE'n created, with 26 rows and 5 columns.

Re: SAS EG prompt - how to use in query builder?

SASKiwi — Tue, 01 May 2018 08:03:24 GMT

Looks like SAS is using the 'Table Name'n type syntax and the macro variable needs double quotes to resolve. Is there an option to change this behaviour?

Re: SAS EG prompt - how to use in query builder?

Wouter — Tue, 01 May 2018 08:47:24 GMT

Yeah, got it now... Looks like a bug, or a very strange way to solve this (that is, in this version??). In the query, hit "Options". Now you'll see:

Output data:

Name: 'HHI_Cr_Institutions_&Rep_date'n

Label: HHI_Cr_Institutions_&Rep_date

Now, hit "Change", and hit "Cancel", and the "Name" will change, equal to the label. Now, click "OK" and don't run the query, just save it. From now on, it will pick up the macro variable...