Cannot insert date in YYMMDD10. format into a table in SAS

Question

I want to insert date values in the YYMMDD10. format into my table. Using a DATE9. format works perfectly for a table, also YYMMDD10. successfully displays the correct/desired output via a 'put', but gives error when trying to insert the same into a table.

SCHEMA:

PROC SQL;   

Create Table Work.Validation_Rules_Record    
    (   
             TEMPLATE_ID varchar ,    
             RULE_ID varchar ,    
             INSTANCE_ID NUM,     
             REPORTING_ENTITY_ID NUM,    
             **REFERENCE_DATE num informat=yymmdd10. format=yymmdd10.,**    
             VALIDATION_RESULT varchar ,    
             VALIDATION_TIME num format=datetime19.2    
     )   ;    


QUIT; 





%LET date_time=%SYSFUNC( DATETIME() );   
%let refr_date='15feb2010'd;   
%put  %sysfunc(putn(&refr_date,yymmdd10.));   
%let ref_date =  %sysfunc(putn(&refr_date,yymmdd10.));   
%put &ref_date;

The INSERT operation below works when using DATE9. as the format but NOT for YYMMDD10. as shown below.

PROC SQL ;

insert into Work.Validation_Rules_Record
        (
             TEMPLATE_ID,
             RULE_ID,
             INSTANCE_ID, 
             REPORTING_ENTITY_ID,
             **REFERENCE_DATE,**
             VALIDATION_RESULT,
             VALIDATION_TIME 
        )
        Values
        (
             'C 01.00', 
             'v_m165' , 
                3395, 
                26, 
            **"&ref_date"d,**

            'C33',
            &date_time.
        )
    ;
    select * from  Work.Validation_Rules_Record ;

QUIT;

Please let me know if you need some more info.

Thanks! KC

Joe Joe · Accepted Answer · 2014-07-07T20:13:29

"<date>"d requires DATE9. exclusively. It doesn't work with other formats. It's not using an informat or anything else; it's a date constant.

If you want to use , use input.

data test;
x='01JAN2013'd;
format x date9.;
run;

proc sql;
insert into test (x)
    values (
        %sysfunc(inputn(2014-01-01,yymmdd10.))
        );
quit;

If you're using this in a normal SAS dataset that is then inserted, use the input function; in a values statement you need to use the %sysfunc(inputn(...)) which uses the macro language to get at it (inputn is the function equivalent of the input statement for numeric functions).

Cannot insert date in YYMMDD10. format into a table in SAS

1 Answers