topic No results still populate a field? in SAS Procedures

No results still populate a field?

mmswan — Fri, 23 Mar 2012 17:58:44 GMT

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!

No results still populate a field?

jcbell — Sat, 24 Mar 2012 12:18:56 GMT

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;

Re: No results still populate a field?

PGStats — Sat, 24 Mar 2012 20:35:30 GMT

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;

Re: No results still populate a field?

Ksharp — Mon, 26 Mar 2012 04:12:54 GMT

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