Desktop productivity for business analysts and programmers

SAS EG prompt - how to use in query builder?

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

SAS EG prompt - how to use in query builder?

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!


Accepted Solutions
Solution
‎05-07-2018 05:58 AM
Contributor
Posts: 66

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

[ Edited ]

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...

View solution in original post


All Replies
Super User
Posts: 24,026

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

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!



 

Contributor
Posts: 66

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

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...

 

Rep_date.JPGPrompt

Retrieve.JPGFirst query in project where Rep_date is created by prompt

Query Builder.JPGThe query builder

Super User
Posts: 24,026

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

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...

 

Rep_date.JPGPrompt

Retrieve.JPGFirst query in project where Rep_date is created by prompt

Query Builder.JPGThe query builder


 

Contributor
Posts: 66

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

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.

Super User
Posts: 4,030

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

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?

Solution
‎05-07-2018 05:58 AM
Contributor
Posts: 66

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

[ Edited ]

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...

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 256 views
  • 0 likes
  • 3 in conversation