Getting selected information from the previous table

[Sandeep77]

Hi all,

I have a dataset and I want only few variables from the previous table. I have written the code but I am getting syntax error. Can you please let me know what am I doing wrong here? 

Proc sql;
create table Linked_for_CF as 
select debt_code as account number, 
dr_address##1 as Address Line1, 
dr_address##2 as Address Line2, 
dr_address##3 as Address Line3, 
dr_address##4 as Address Line4, 
dr_postcode as Postcode, 
dr_title as Title, 
Dr_inits as Forename, 
Dr_name as Surname,
Dr_DOB as Date of birth
from Linked_acc;
quit;
Error log:
29         Proc sql;
30         create table Linked_for_CF as
31         select debt_code as account number,
                                       ______
                                       22
ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.  

32         dr_address##1 as Address Line1,
                     __
                     22
                     76
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, 
              HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.  

ERROR 76-322: Syntax error, statement will be ignored.

33         dr_address##2 as Address Line2,
34         dr_address##3 as Address Line3,
35         dr_address##4 as Address Line4,
36         dr_postcode as Postcode,
37         dr_title as Title,
38         Dr_inits as Forename,
39         Dr_name as Surname,
40         Dr_DOB as Date of birth
41         from Linked_acc;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
2                                                          The SAS System                               08:50 Thursday, May 25, 2023

42         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              326.34k
      OS Memory           31332.00k
      Timestamp           05/25/2023 03:21:53 PM
      Step Count                        29  Switch Count  0
      
43         
44         %LET _CLIENTTASKLABEL=;
45         %LET _CLIENTPROCESSFLOWNAME=;
46         %LET _CLIENTPROJECTPATH=;
47         %LET _CLIENTPROJECTPATHHOST=;
48         %LET _CLIENTPROJECTNAME=;
49         %LET _SASPROGRAMFILE=;
50         %LET _SASPROGRAMFILEHOST=;
51         
52         ;*';*";*/;quit;run;
53         ODS _ALL_ CLOSE;
54         
55         
56         QUIT; RUN;
57         

[ballardw · Accepted solution]

SAS variable names may start with the _ character or a letter, followed by _, letter or digit. You have spaces in "account number" , "Address Line1" and others. Plus the # character in other variables.

IF you set the SAS system option VALIDVARNAME=ANY then you may use name literals which require you to place quotes around the name with invalid characters followed by the letter n to indicate that is what you mean: a name literal for a variable.

This would look like

'Account Number'n

every time you use that variable. Personally I don't think it worth it though may be needed to work with external data sources.

If the purpose of attempting those names with spaces is make "nice" labels for variable then use the LABEL option when creating the variable such as

select debt_code as account_number label='Account Number', 

but I have no idea why you might attempt to use ## in a variable name.