How do I display dates in PROC FEDSQL?

Question

How do I change the display format of a date in PROC FEDSQL? I want to keep it as a numeric variable, but to display a date format (preferably similar to date9. in PROC SQL)

Answer 1

I take "Keep it as numeric" to mean you are creating a table.

Suppose you use the DATEPART() function to extract the date value from a datetime value. In FEDSQL the selection tacitly formats the resultant with DATE9.

Example:

data dates;
  do timestamp = intnx('dtday', datetime(), -14) to datetime() by '24:00:00't;
    output;
  end ;
  format timestamp E8601dt.;
run;

ods listing;

proc fedsql ;
  select timestamp from dates;
  drop table work.x;
  create table work.x as select datepart(timestamp) as date from dates;
  select date from work.x;
quit;

%let syslast = work.x;

You can also use Proc DATASETS after you quit Proc FEDSQL to set the format of a variable.