SAS insert value with proc sql

Question

So I have a rather interesting problem. I am trying to insert a current date in specific formats and styles, but for some reason it seems to fail. I know its not a formatting issue... But idk how to fix it. a data step solution is welcomed as well... Here's what works.

proc sql;
create table work.test
(test_Id char(50), test_Name char(50), cur_Mo char(1), cur_Qtr char(1), entered_Date char(8));
insert into work.test 
values('201703','2017 Mar','0','0','24APR17')
values('201704','2017 Apr','0','0','24APR17')
values('201706','2017 Jun','1','0','23JUN17');
quit;

Here's what doesn't:

proc sql;
    insert into work.test 
    values(catx('',put(year(today()),4.),case when month(today())< 10 then catx('','0',put(month(today()),2.)) else put(month(today()),2.)end) ,catx(' ',[put(year(today()),4.),put(today(),monname3.))],'1','0',put(today(),date7.));
quit;

Answer 1

You can use the %SYSFUNC() macro function to call most other SAS function in macro code. So to generate today's date in DATE7 format you could use:

insert into work.test (date)
  values("%sysfunc(date(),date7)")
;

Answer 2

The way I'd probably do it is to use a data step to make a dataset that you would insert, and then insert that dataset.

You can use insert into (...) select (...) from (...) syntax in SAS, and the data step is much more flexible as to allowing you to define columns.

For example:

proc sql;
  create table class like sashelp.class;
quit;

proc sql;
   insert into class
     select * from sashelp.class;
quit;

Or you can specify only certain variables:

proc sql;
    insert into class (name, age)
      select name, age from sashelp.class;
quit;



data to_insert;
  name= 'Wilma';
  sex = 'F';
  age = 29;
  height = 61.2;
  weight = 95.3;
run;

proc sql;
  insert into class
    select * from to_insert;
quit;

Just make sure you either explicitly list the variables to insert/select, or you have the order exactly right (it matches up by position if you use * like I do above).