proc sql create table select with a blank column

[Rebecca_K]

Hello!  I am trying to run a proc sql with a select, so that I can grab all fields from another table, and also include a new 50 long character "blank" column.   This code is giving errors, any ideas for fix, please? 

proc sql ;
Create Table Want as
Select
'' as 'New_Field' format $50.,
*
From Have; 
quit;

ERROR 22-322: Expecting a name.  

ERROR 200-322: The symbol is not recognized and will be ignored.

[ballardw]

If you are going to use name literals then you end them with an N immediately after the closing quote:

proc sql ;
Create Table Want as
Select
'' as 'New_Field'n format $50.,
*
From Have; 
quit;

This will require the system options VALIDVARNAME=ANY if not already set.

[PaigeMiller]

proc sql ;
Create Table Want as
Select
'' as 'New_Field'n length=50,
*
From Have; 
quit;

You don't new to include NEW_FIELD inside quotes followed by the letter N, simply use New_Field as the name. Don't make your code more difficult to type than it needs to be.

[Tom]

---

@ballardw wrote:

... will require the system options VALIDVARNAME=ANY if not already set.

---

Only if the name is non-standard.

Try this:

options validvarname='V7';
proc sql;
create table want as
  select ' ' as 'new_field'n length=50
       , getoption('validvarname') as varname length=5
       , *
  from sashelp.class(obs=1)
;
quit;
proc print;
run;

[Kurt_Bremser]

No quotes around variable names.

proc sql ;
Create Table Want as
Select
'' as New_Field length=50,
*
From Have; 
quit;

And do not use formats to define the length.

[Tom · Accepted solution]

Use the LENGTH option to set the length.  There is no need to attach a format as SAS already knows how to display character strings.

No need to place the quotes next to each other when typing the string literal for the blank string. SAS variables are fixed length and padded with spaces.   So just use

' '

to indicate an blank string and you won't get confused looking at the code whether that is two single quote characters next to each other or one double quote character.

No need to add quotes around the variable name (as long as it is a valid SAS name).   

proc sql ;
create table want as
  select ' ' as New_Field length=50
       , have.*
  from have
; 
quit;

If the name is non standard then you could either use a name literal. 

' ' as 'New_Field'n

Or add the DQUOTE=ANSI option to the PROC SQL statement and use double quote characters around the variable name.  But in that case make sure to use single quotes for any string literals.

proc sql dquote=ansi;
create table want as
  select ' ' as "New_Field" length=50
       , have.*
  from have
; 
quit;

Note if you do want to create a variable name like 'New Field'n then make sure you have set the VALIDVARNAME option to ANY.

[Tom]

Why use SQL?

data want;
  length New_Field $50 ;
  set have;
run;