topic Re: PROC SQL - Double select not working in SAS Programming

PROC SQL - Double select not working

jorquec — Mon, 12 Dec 2022 11:25:23 GMT

Hi all,

Could you please let me know why this code is not working? I can't spot the error 😞

proc sql ;
select
  usi,product_name,device_launch_date,price_at_launch
from (
	select
  	h.usi,  
  	lower(h.product_name) as product_name, d.device_launch_date, d.price_at_launch
  	from cdm_uat2.product_holding_track h
  	left join cdm_uat2.device_ref d
  	on h.product_id= d.sku
  	where d.product_type_1= 'handset') a; 
quit;

log is not helpful

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

82 proc sql ;

83 select

84 usi,product_name,device_launch_date,price_at_launch

---

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS,

CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

85 from (

ERROR 22-322: Syntax error, expecting one of the following: ',', EXCEPT, GROUP, HAVING, INTERSECT, JOIN, ORDER, UNION, WHERE.

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

86 select

87 h.usi,

88 lower(h.product_name) as product_name, d.device_launch_date, d.price_at_launch

89 from cdm_uat2.product_holding_track h

90 left join cdm_uat2.device_ref d

91 on h.product_id= d.sku

92 where d.product_type_1= 'handset') a;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

93 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.01 seconds

95 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

111

Re: PROC SQL - Double select not working

PaigeMiller — Mon, 12 Dec 2022 12:33:51 GMT

From now on, please preserve the formatting of the log (making it easier to read) by clicking on the </> icon and pasting the log into the window that appears. We're trying to help you, but you have to help us.

There is no LOWER function in SAS. You need to use the LOWCASE function.

Re: PROC SQL - Double select not working

FreelanceReinh — Mon, 12 Dec 2022 12:31:48 GMT

Hi @jorquec,

Good news: Your code works for me with dummy data that I've created.

But I am using a local SAS 9.4 installation with the traditional Display Manager interface. I suspect that you are using SAS Studio or a similar browser-based user interface so that the tab characters, which you used for code indentation, or maybe other invisible characters in your code are not treated as blanks, hence the error messages.

Remove at least the leading white-space characters in each line and retype them as ordinary spaces. Or use the "search and replace" feature of your user interface to replace tabs with spaces.

To make similar issues less likely to occur in the future, modify the editor options regarding tab characters as shown by Tom in Re: SAS will not read my 3 columns of data. And in general, avoid tab characters in SAS code.

Re: PROC SQL - Double select not working

gema — Tue, 10 Jan 2023 19:09:52 GMT

there is a lower (and upper) function in SAS data step and non-passthrough SQL. pass-through uses upcase and lowcase

Re: PROC SQL - Double select not working

Kurt_Bremser — Tue, 10 Jan 2023 19:39:10 GMT

There is a LOWCASE and UPCASE function in the SAS documentation, but you won't find LOWER or UPPER.

Re: PROC SQL - Double select not working

Tom — Tue, 10 Jan 2023 20:16:59 GMT

I copied the text you posted and saved it to a file. It appears to have some random TAB characters inserted.

421  data _null_;
422    infile 'c:\downloads\sql.sas';
423    input;
424    list;
425  run;

NOTE: The infile 'c:\downloads\sql.sas' is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         proc sql ; 10
2         select 6
3           usi,product_name,device_launch_date,price_at_launch 53
4         from ( 6
5   CHAR  .select 7
    ZONE  0766667
    NUMR  935C534

6   CHAR    .h.usi,   11
    ZONE  22062776222
    NUMR  0098E539C00

7   CHAR    .lower(h.product_name) as product_name, d.device_launch_date, d.price_at_launch 81
    ZONE  220667672627766767566662267277667675666622626676665667666566762262776665675667666
    NUMR  009CF75288E02F4534FE1D59013002F4534FE1D5C04E456935FC15E38F4145C04E02935F14FC15E38

8   CHAR    .from cdm_uat2.product_holding_track h 40
    ZONE  2206766266657673277667675666666657766626
    NUMR  00962FD034DF5142E02F4534F8FC49E7F4213B08

9   CHAR    .left join cdm_uat2.device_ref d 34
    ZONE  2206667266662666576732667666576626
    NUMR  009C5640AF9E034DF5142E456935F25604

10  CHAR    .on h.product_id= d.sku 25
    ZONE  2206626277667675663262767
    NUMR  009FE08E02F4534F94D04E3B5

11  CHAR    .where d.product_type_1= 'handset') a;  41
    ZONE  22076676262776676757776533226666767222632
    NUMR  0097852504E02F4534F4905F1D0781E43547901B0
12        quit; 5
NOTE: 12 records were read from the infile (system-specific pathname).
      The minimum record length was 5.
      The maximum record length was 81.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

With the editor window in SAS/Studio you can actually embed tab characters into the file and the way that SAS/Studio works behind the scenes to send the code to SAS to execute does NOT replace the tabs with spaces

 69         data _null_;
 70           infile cards;
 71           input;
 72           list;
 73         cards4;
 
 RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 74         proc sql ;
 75         select
 76           usi,product_name,device_launch_date,price_at_launch
 77         from (
 
 78  CHAR   .select                                                                         
     ZONE   07666672222222222222222222222222222222222222222222222222222222222222222222222222
     NUMR   935C5340000000000000000000000000000000000000000000000000000000000000000000000000
 
 79  CHAR     .h.usi,                                                                       
     ZONE   22062776222222222222222222222222222222222222222222222222222222222222222222222222
     NUMR   0098E539C00000000000000000000000000000000000000000000000000000000000000000000000
 
 80  CHAR     .lower(h.product_name) as product_name, d.device_launch_date, d.price_at_launch                   
     ZONE   2206676726277667675666622672776676756666226266766656676665667622627766656756676662222222222222222222
     NUMR   009CF75288E02F4534FE1D59013002F4534FE1D5C04E456935FC15E38F4145C04E02935F14FC15E380000000000000000000
       101                                                              
 
 81  CHAR     .from cdm_uat2.product_holding_track h                                                            
     ZONE   2206766266657673277667675666666657766626222222222222222222222222222222222222222222222222222222222222
     NUMR   00962FD034DF5142E02F4534F8FC49E7F4213B08000000000000000000000000000000000000000000000000000000000000
       101                                                              
 
 82  CHAR     .left join cdm_uat2.device_ref d                                                                  
     ZONE   2206667266662666576732667666576626222222222222222222222222222222222222222222222222222222222222222222
     NUMR   009C5640AF9E034DF5142E456935F25604000000000000000000000000000000000000000000000000000000000000000000
       101                                                              
 
 83  CHAR     .on h.product_id= d.sku                                                                           
     ZONE   2206626277667675663262767222222222222222222222222222222222222222222222222222222222222222222222222222
     NUMR   009FE08E02F4534F94D04E3B5000000000000000000000000000000000000000000000000000000000000000000000000000
       101                                                              
 
 84  CHAR     .where d.product_type_1= 'handset') a;                                                            
     ZONE   2207667626277667675777653322666676722263222222222222222222222222222222222222222222222222222222222222
     NUMR   0097852504E02F4534F4905F1D0781E43547901B000000000000000000000000000000000000000000000000000000000000
       101                                                              
 85         quit;
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

(unlike when you do the same thing with the normal SAS editor in SAS Display Manager).

438  data _null_;
439    infile cards;
440    input;
441    list;
442  cards4;

RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
443        proc sql ;
444        select
445          usi,product_name,device_launch_date,price_at_launch
446        from (
447          select
448            h.usi,
449            lower(h.product_name) as product_name, d.device_launch_date, d.price_at_launch
450            from cdm_uat2.product_holding_track h
451            left join cdm_uat2.device_ref d
452            on h.product_id= d.sku
453            where d.product_type_1= 'handset') a;
454        quit;

But that is probably NOT your problem as (at least with SAS/Studio 3.71) the tabs in CODE do not cause any trouble.

I suspect that some of the spaces that you posted might actually be some other invisible character. That is confusing SAS.

Re: PROC SQL - Double select not working

Tom — Tue, 10 Jan 2023 20:30:24 GMT

PROC SQL supports the LOWER() function.

Note: The LOWER function is provided for compatibility with the ANSI SQL standard. You can also use the SAS function LOWCASE.

But in normal SAS code you need to use the real SAS function and not the SQL function.