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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 819 views
  • 2 likes
  • 3 in conversation