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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.