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;

You cannot put functions in a VALUES clause. Just actual values. — Tom, Jul 11 '17 at 15:56
I figured that would be the case, is there another approach? — RGM-79FP GM Striker, Jul 11 '17 at 15:57

score 2 · Accepted Answer · answered Jul 11 '17 at 16:02

2

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)")
;

answered Jul 11 '17 at 16:02

Tom

47,574
2
16
29

I do have a follow up - how do I combine the month and date so that it follows: 201701,201702 and so on? – RGM-79FP GM Striker Jul 11 '17 at 17:21
SAS has lots of formats for dates. YYMMN6 would generate date strings in the format of YYYYMM. – Tom Jul 11 '17 at 19:42
In my case my solution was to use this in an Insert Statement: `values("%sysfunc(date(),yymmn6.)","%sysfunc(date(),yymon7.)",'1','0',"%sysfunc(date(),date7.)");` For now this meets the requirements of my current problem. Thanks – RGM-79FP GM Striker Jul 14 '17 at 14:35

score 1 · Answer 2 · answered Jul 11 '17 at 16:17

1

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).

answered Jul 11 '17 at 16:17

Joe

62,789
6
49
67

Thanks for the advice, what if there was a date function involved as one of the variables? I'll be trying this as well! Thanks! – RGM-79FP GM Striker Jul 11 '17 at 16:54
1

In the data step that's not a problem; the date function should resolve in the data step (i.e., don't insert the text "today()", but `age=today()-birthdate;` should be fine, since it does the calculation in the data step). – Joe Jul 11 '17 at 17:04
If you are inserting data from a table using a `SELECT` instead of constants using `VALUES` then you can use functions. – Tom Jul 11 '17 at 19:45

SAS insert value with proc sql

2 Answers2