Insert into function with SAS/SQL

Question

I want to insert values into a new table, but I keep getting the same error: VALUES clause 1 attempts to insert more columns than specified after the INSERT table name. This is if I don't put apostrophes around my date. If I do put apostrophes then I get told that the data types do not correspond for the second value.

    proc sql;
    create table date_table
    (cvmo char(6), next_beg_dt DATE);
    quit;

    proc sql;
      insert into date_table 
    values ('201501', 2015-02-01)
    values ('201502', 2015-03-01)
    values ('201503', 2015-04-01)
    values ('201504', 2015-05-01);
    quit;

The second value has to remain as a date because it used with > and < symbols later on. I think the problem may be that 2015-02-01 just isn't a valid date format since I couldn't find it on the SAS website, but I would rather not change my whole table.

The first of February of 2015 is represented by `'01FEB2015'd` in SAS code. You need a quoted text with the letter `d` after it. The text must by in a format that is recognized by the DATE informat. — Tom, Nov 07 '18 at 21:01

score 3 · Answer 1 · answered Nov 07 '18 at 21:13

Date literals (constants) are quoted strings with the letter d immediately after the close quote. The string needs to be in a format that is valid for the DATE informat.

'01FEB2015'd
"01-feb-2015"d
'1feb15'd

If you really want to insert a series of dates then just use a data step with a DO loop. Also make sure to attach one of the many date formats to your date values so that they will print as human understandable text.

data data_table ;
  length cvmo $6 next_beg_dt 8;
  format next_beg_dt yymmdd10.;
  do _n_=1 to 4;
    cvmo=put(intnx('month','01JAN2015'd,_n_-1,'b'),yymmn6.);
    next_beg_dt=intnx('month','01JAN2015'd,_n_,'b');
    output; 
  end;
run;

score 2 · Answer 2 · answered Nov 07 '18 at 22:17

@tom suggest you in comments how to use date and gives very good answer how to it efficently, which is less error prone than typing values. I am just putting the same into the insert statement.

proc sql;
create table date_table
(cvmo char(6), next_beg_dt DATE);
quit;

proc sql;
  insert into date_table 
values ('201501', "01FEB2015"D)
;

Insert into function with SAS/SQL

2 Answers2