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!
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;
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.