topic SQL Insert not working in SAS Programming

SQL Insert not working

Reeza — Wed, 28 Aug 2013 22:28:57 GMT

Why doesn't the following work? My brains gone fuzzy...
Thanks!

proc sql;

create table Table1

(Account char(50) label="Transaction File",

Count num label="Number of Records");

proc sql;

insert into Table1

set Account='Total Number of Records submitted', count=&row.

set Account='Number of facility', count=&row1.

set Account='Number of Primary Account', count=&row2

set Account='Number of Secondary Financial Account', count=&row3

set Account='Number of Secondary Statistical Account', count=&row4;

quit;

627 proc sql;

628 insert into Table1

629 set Account='Total Number of Records submitted', count=&row.

630 set Account='Number of facility', count=&row1.

631 set Account='Number of Primary Account', count=&row2

-------

632 set Account='Number of Secondary Financial Account', count=&row3

-------

ERROR 22-322: Syntax error, expecting one of the following: (, ',', SET.

633 set Account='Number of Secondary Statistical Account', count=&row4;

-------

ERROR 22-322: Syntax error, expecting one of the following: (, ',', SET.

633! set Account='Number of Secondary Statistical Account', count=&row4;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, MISSING,

NULL, PUT, SUBSTRING, USER.

634 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Re: SQL Insert not working

ballardw — Wed, 28 Aug 2013 22:57:27 GMT

Is there any chance that &row1 is resolving to something other than a numeric?

Re: SQL Insert not working

Jagadishkatam — Thu, 29 Aug 2013 04:31:15 GMT

I think the problem is with the macro variables not resolving at all.

I tried to give some values to the macro variables and it worked well. Please check the code below

333 %let row1=1;

334 %let row2=2;

335 %let row3=3;

336 %let row4=4;

337 %let row5=5;

338

339 proc sql;

340

341 create table Table1

342

343 (Account char(50) label="Transaction File",

344

345 Count num label="Number of Records");

NOTE: Table WORK.TABLE1 created, with 0 rows and 2 columns.

346

NOTE: PROCEDURE SQL used (Total process time):

real time 0.02 seconds

cpu time 0.03 seconds

347 proc sql;

348

349 insert into Table1

350

351 set Account='Total Number of Records submitted', count=&row1.

352

353 set Account='Number of facility', count=&row2.

354

355 set Account='Number of Primary Account', count=&row3.

356

357 set Account='Number of Secondary Financial Account', count=&row4.

358

359 set Account='Number of Secondary Statistical Account', count=&row5.;

NOTE: 5 rows were inserted into WORK.TABLE1.

360

361 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

Thanks,

Jagadish