Desktop productivity for business analysts and programmers

Advanced Query to Create New Column

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

Advanced Query to Create New Column

Hey All,

I need some help.  I am creating a report in where i have imported data... everything there is good... the only thing is the data that i am importing does not have a date in it.   What I am trying to do is to tell SAS what date to put into the report with a date prompt, so when i run the project... it will prompt me for a date, and that date will insert into that column.

I am completely lost.  Can anyone help with the syntax for the advanced expression?

Dean


Accepted Solutions
Solution
‎08-09-2013 03:18 AM
Frequent Contributor
Posts: 120

Re: Advanced Query to Create New Column

Hi!

The steps to use the prompt in the query builders advanced expression might look like this:

1. Create the prompt (in this case a date prompt called prompt_date). Check the two boxes "..non-blank value" and "Use prompt...". Save the prompt.

2. Create the query and the calculated value. Set the calculated value to "&prompt_date"d. Save and close the query, ignore the error message...

3. Right click the query and select properties. Select prompts and add the prompt you created.

4. Run the query.

//Fredrik

View solution in original post


All Replies
Super Contributor
Posts: 307

Re: Advanced Query to Create New Column

This isn't difficult to do if I understand your question properly.

In this example, I've created a prompt in EG 4.3. called mydate and set it to date format with a default of today's date.

/* manufacture some data */

data have;
drop i j;
do i = 1 to 3 ;
  do j = 1 to 10 ;
   id + 1 ;
   x=ceil(100*rand("Uniform"));
   output;
  end;
end;
run;

/* add the date to a new column */

data want;
set have;
rundate = "&mydate";
run;

Contributor
Posts: 68

Re: Advanced Query to Create New Column

Thanks for you response... i was looking through one of the imports and it had a date in an odd location of the date for the report... i just imported it and added it to the report... works like a charm.

Anyway, I was looking for the string to enter in the expression builder... i didn't want to do straight code on that... i got a little confused... i am more beginner to intermediate... but thanks anyways... appreciate the time.

Dean

Respected Advisor
Posts: 3,124

Re: Advanced Query to Create New Column

@Fugue 's solution is for Stored Process, where you can also apply prompts. You were probably taking about prompts in query builder. Stored Process is much more flexible, therefore more powerful than preset query builder, you will have to get used to it moving forward.

Haikuo

Solution
‎08-09-2013 03:18 AM
Frequent Contributor
Posts: 120

Re: Advanced Query to Create New Column

Hi!

The steps to use the prompt in the query builders advanced expression might look like this:

1. Create the prompt (in this case a date prompt called prompt_date). Check the two boxes "..non-blank value" and "Use prompt...". Save the prompt.

2. Create the query and the calculated value. Set the calculated value to "&prompt_date"d. Save and close the query, ignore the error message...

3. Right click the query and select properties. Select prompts and add the prompt you created.

4. Run the query.

//Fredrik

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 1019 views
  • 3 likes
  • 4 in conversation