topic Re: inserting values a table using a data in proc fedsql in SAS Programming

inserting values a table using a data in proc fedsql

Aexor — Tue, 12 Oct 2021 18:55:04 GMT

I am getting error while inserting data using proc fedsql

Proc fedsql;

create table paylist

(IdNum char(4),

Gender char(1),

Jobcode char(3),

Salary double,

Birth date,

Hired date);

insert into paylist

values('1639','F','TA1',42260,date'26JUN70',date'28JAN91')

values('1065','M','ME3',38090,date'26JAN54',date'07JAN92')

values('1400','M','ME1',29769,date'05NOV67',date'16OCT90')

values('1561','M',null,36514,date'30NOV63',date'07OCT87')

values('1221','F','FA3',.,date'22SEP63',date'04OCT94');

quit;

error : syntax error at or near values.

Please help me to resolve this issue.

Re: inserting values a table using a data in proc fedsql

ChrisNZ — Tue, 12 Oct 2021 22:57:07 GMT

Like this?

INSERT INTO MyTable ( Column1, Column2 ) VALUES ( Value1, Value2 ), ( Value1, Value2 )

Re: inserting values a table using a data in proc fedsql

Patrick — Tue, 12 Oct 2021 23:52:31 GMT

According to the documentation here you have to repeat the insert into statement. Additionally the date string format must be as in below code.

proc datasets lib=work nolist nowarn;
  delete paylist;
  run;
quit;

Proc fedsql;
  create table paylist
    (IdNum char(4),
    Gender char(1),
    Jobcode char(3),
    Salary double,
    Birth date,
    Hired date)
    ;
  insert into paylist values ('1639','F','TA1',42260,date'1970-06-26',date'1991-01-28');
  insert into paylist values ('1639','F','TA1',42260,date'1970-06-26',date'1991-01-28');
quit;

Re: inserting values a table using a data in proc fedsql

ChrisNZ — Wed, 13 Oct 2021 02:53:34 GMT

Oh that's (slightly) disappointing. The comma separated syntax would be useful (and potentially faster).

I should have tested, but then I never use proc fedsql, and I simply assumed this standard syntax would be supported. My bad.