BookmarkSubscribeRSS Feed
RobertWF1
Quartz | Level 8

I’m trying to upload a SAS table to Redshift using the following simple command:

 

data rf.rsv_standing_cohort;
set epi.rsv_standing_cohort;
run;

where the “epi” library is on my local server and the “rf” library refers to my Redshift schema.

 

However, I’m getting the following error – part of my table is not being uploaded (original table size is 35,392 records). 

 

Any ideas why the table upload is incomplete?

 

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: CLI execute error: [SAS][ODBC Redshift Wire Protocol driver]This is either an Unknown Type or is not supported currently.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 32750 observations read from the data set EPI.RSV_STANDING_COHORT.
WARNING: The data set RF.RSV_STANDING_COHORT may be incomplete.  When this step was stopped there were 32749 observations and 9 variables.
ERROR: ROLLBACK issued due to errors for data set RF.RSV_STANDING_COHORT.DATA.
NOTE: DATA statement used (Total process time):
       real time           1:35.32
       cpu time            0.09 seconds

 

4 REPLIES 4
SASKiwi
PROC Star

On the evidence you've posted it appears there is some data in row 32,750 of your SAS dataset that is causing a Redshift CLI error. I suggest you try loading just 32,749 rows from SAS and see if that works without errors:

data rf.rsv_standing_cohort;
set epi.rsv_standing_cohort (obs = 32749);
run;

If it does, then try this test to see if it fails on the first row:

data rf.rsv_standing_cohort;
set epi.rsv_standing_cohort (firstobs = 32750);
run;

If it does, then I suggest you examine the data in row 32,750 to see if you can spot what might be causing the error. Foreign language character might be one possibility.

quickbluefish
Barite | Level 11

@RobertWF1  - I'm curious if you managed to resolve this issue?  I am having the exact same thing happen - stopping at record number 32,499, in this case.  I have tried specifying the data type for each variable using the DBTYPE=() data statement option, but same thing.  It also doesn't matter if I pass a different segment of the SAS dataset (e.g. _N_>33,000) -- it still stops at 32,499.   I have looked at the records around that number and nothing seems amiss.  I assume it's a space issue on the Redshift side, but postgreSQL does not seem to have any real method of returning the amount of free space available in a given schema.  

SASKiwi
PROC Star

Are you using a DATA step as well? What's your INSERTBUFF setting if any on your Redshift connection?

 

quickbluefish
Barite | Level 11

@SASKiwi  - thanks for your reply - yes, using DATA step for this.  I actually haven't tried adjusting the INSERTBUFF at all - good idea - will try!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 725 views
  • 2 likes
  • 3 in conversation