No results still populate a field?

[mmswan]

I need the RunDate field to populate when the report was run, even if there are no results in order to show that it was run for each day.

proc sql;

create table work.test as select distinct query_for_test1

query_for_test1.field1

query_for_test1.field2

(datetime()) formate=datetime16.0 as RunDate

from work.query_for_test1 as query_for_test1

Thank you!

[jcbell]

I think you may need a couple of steps:

Something like this should work:

proc sql;

create table work.test

(query_for_test1 char(10),... etc..

rundate NUM  format=datetime16.);

insert into work.test

(rundate)

   values(%sysfunc(datetime()));

insert into work.test

from work.query_for_test1.... etc. (real data)

quit;

[PGStats]

Using a left join does the trick. A single observation table is created containing the current time. Then it is left-joined with the result from your query (the join condition on 1 always matches). If your query returns empty then a single observation is created with the current time, all other fields are missing. Try the folowing code by changing the query condition from i > 12 to i < 5.

data test;
do i = 1 to 10; output; end;
run;

proc sql;

create table rundate (rundate date format=datetime20.0);

insert into rundate set rundate=datetime();

create table qry as
select A.rundate, B.i from
rundate as A left join (select i from test where i > 12) as B on 1;

drop table rundate;

quit;

PG

[Ksharp]

You need comma to split these columns.

proc sql;
 create table x as
  select name,sex,age,datetime() format=datetime. as runtime
   from sashelp.class;
quit;

Ksharp