Hello,
Has anyone dealt with how to reference tables names larger than 32 characters on redshift through proc sql?
Thanks.
Susan
BASE SAS V9 using ODBC connecting to Amazon redshift via proc sql and a libname statement.
proc sql;
create table nutslp as
select blah
from prod.member m
inner join prod.member_activities_by_activitytype ma
on a.action_type = ma.activitytype
;
quit;
partial log below:
637 inner join prod.member_activities_by_activitytype ma
---------------------------------
65
ERROR 65-58: Name 'MEMBER_ACTIVITIES_BY_ACTIVITYTYPE' is too long for a SAS name in this context.
You've coded it wrong:
proc sql;
connect to ODBC as myred (datasrc='bbb' user=me password=ppp );
create table nutslp as
select *
from connection to myred
(select *
from member m
inner join member_activities_by_activitytype ma
on m.action_type = ma.activitytype
)
;
quit;
Thanks but the explicit sql pass through did not seem to work. Unless I coded it wrong. I got the same message as before.
proc sql;
connect to ODBC as myred (datasrc='bbb' user=me password=ppp );
create table nutslp as
select *
from member m
inner join member_activities_by_activitytype ma
on m.action_type = ma.activitytype
;
quit;
You've coded it wrong:
proc sql;
connect to ODBC as myred (datasrc='bbb' user=me password=ppp );
create table nutslp as
select *
from connection to myred
(select *
from member m
inner join member_activities_by_activitytype ma
on m.action_type = ma.activitytype
)
;
quit;
Thank you SASKiwi, worked like a charm 🙂
Glad to be of service!
Hi, I have a similar problem.
I just moved to a new company where we don't have a scratch DB on the SQl server. To surcumvent this, I used to upload my table with the primary key to the scratch DB; then connect to the DB and execute SQL on the server to join my table; then pull it to my local machine. However, as I mentioned, we don't have a scratch DB here.
I tried the above example, but it still gives me an error...i hope you can help:
/*example*/
data tableA (drop= i);
do i = 1 to 10;
sys_ID = i;
output;
end;
run;
proc sql;
connect to odbc as imaging (dsn=imaging);
create table tableB as
select *
from connection to imaging
(select *
from work.tableA as a
left join imaging.dbo.AdviceSheetAdditionalRequirements b
on a.sys_ID = b.sys_ID
)
order by a.sys_ID
;
disconnect from odbc;
quit;
The Log window says:
ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'work.tableA'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.
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.