DATA Step, Macro, Functions and more

SQL Insert not working

Accepted Solution Solved
Reply
Super User
Posts: 17,819
Accepted Solution

SQL Insert not working

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

             -------

             22

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

             -------

             22

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

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

             -------

             22

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

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

                                                                           -

                                                                           22

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


Accepted Solutions
Solution
‎08-29-2013 12:31 AM
Trusted Advisor
Posts: 1,128

Re: SQL Insert not working

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

Thanks,
Jag

View solution in original post


All Replies
Super User
Posts: 10,500

Re: SQL Insert not working

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


Solution
‎08-29-2013 12:31 AM
Trusted Advisor
Posts: 1,128

Re: SQL Insert not working

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

Thanks,
Jag
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 426 views
  • 0 likes
  • 3 in conversation