SAS insert date format

Question

I have the following codes:

PROC SQL;

    CREATE TABLE Hub_Category2 (
    CategoryID INT NOT NULL,
    CategoryName VARCHAR(15) NOT NULL,
    LOAD_DATE NUM FORMAT=DATETIME22. NOT NULL,
    RECORD_SOURCE VARCHAR(255) NOT NULL);
RUN;

quit;

%let "LOAD_DATE: %sysfunc(datetime(),datetime22.)"; 

%let RECORD_SOURCE='123'; 

proc sql;

    CREATE VIEW VIEW_HUB_CATEGORIES AS
    SELECT 
    CategoryID,
    CategoryName,
    &LOAD_DATE as LOAD_DATE, 
    &RECORD_SOURCE as RECORD_SOURCE 
    FROM  LIB.CATEGORIES;
RUN;
Quit;

When I run the following code

proc sql;

  insert into Hub_Category2 select * from VIEW_HUB_CATEGORIES;

  run;

Quit;

It is giving the following error.

proc sql;
    72         insert into Hub_Category2 select * from VIEW_HUB_CATEGORIES;
 ERROR: Value 3 on the SELECT clause does not match the data type of the 
corresponding column listed after the INSERT table name.

I think, I made mistake while formatting date, inserting or using macro. Please, help me

Answer 1

The macro variable LOAD_DATE is either not present, or incorrectly valued with respect to source code generation.

Try

%let LOAD_DATE = %sysfunc(datetime());  %* macro variable value is source code (a bunch of digits) representing current datetime;
%let RECORD_SOURCE = '123';             %* macro variable value is source code for a single quoted string literal;

proc sql;
    CREATE VIEW VIEW_HUB_CATEGORIES AS
    SELECT 
    CategoryID,
    CategoryName,
    &LOAD_DATE as LOAD_DATE, 
    &RECORD_SOURCE as RECORD_SOURCE 
    FROM  LIB.CATEGORIES;
RUN;

The value being placed in the LOAD_DATE column at

&LOAD_DATE as LOAD_DATE

does not need to be formatted for human readability, it needs to be the date time value itself.