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
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.