2

It seems as though I cannot use a SAS function in an insert statement:

proc sql;

     create table tq84_tab (col char(100));
     insert into  tq84_tab values (repeat('foo ', 10));

quit;

When I run the code, I am getting:

insert into  tq84_tab values (repeat('foo ', 10));
  ----     -----
   22       26
  202      200  

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, 
              a missing value, +, -, MISSING, NULL, USER.   
ERROR 200-322: The symbol is not recognized and will be ignored.
ERROR 202-322: The option or parameter is not recognized and will be ignored.

Am I doing something wrong or is my suspiscion indeed the case?

Ali Azam
  • 2,047
  • 1
  • 16
  • 25
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293

2 Answers2

1

For an item to appear N times you repeat it N-1 times. You'll also need to macro quote the item if you want it to repeat a trailing space:

insert into tq84_tab values ("%sysfunc(repeat(%str(foo ), 9))");

You can also create a dummy table with only one row. Then use run-time functions to construct the value to insert. 

create table onerow (ignore_me char(0));
insert into onerow values ('');
insert into tq84_tab select (repeat("foo ",9)) as col from onerow;

onerow is acting as Oracle's DUAL or SQL Server's bare select (no from).

Richard
  • 25,390
  • 3
  • 25
  • 38
0

No/Maybe.

With insert .. values you have to you fixed values, no functions.

In this simple case you'd be able to help yourself with SAS macro - call the SAS function using %sysfunc macro function, that "pre-processes" your code.

proc sql;
     create table tq84_tab (col char(100));
     insert into  tq84_tab values ("%sysfunc(repeat(foo, 10))");
     select * from tq84_tab;
quit;
vasja
  • 4,732
  • 13
  • 15
  • @René Nyffenegger : Is the goal to create ONE VARIABLE containing "foo" repeated 10 times (foofoofoofoofoofoofoofoofoofoo) or to create 10 VARIABLES each one contains the world "foo"? – D. O. Nov 27 '17 at 16:00
  • *One* variable with the value `foo foo foo foo foo foo foo foo foo foo `. – René Nyffenegger Nov 27 '17 at 16:24