Advanced Query to Create New Column

[D_Z_]

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

[Fugue]

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;

[D_Z_]

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

[Haikuo]

@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

[FredrikE · Accepted solution]

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