Hi,
I am trying to load Teradata table from SAS wrkmrg.ava_prty_lt3 TABLE and getting below error but there is no value in the table which is missing. what is wrong here?
I am not sure, but the problem may stem from the REQUEST_ID variable. LENGTH=10 is not applicable to numeric variables. Is the variable numeric on Teradata? If not, you should convert it to character. What exactly are you trying to do with that variable?
Other than that, if you have a problem with missing values, it may be that the Teradata table has more columns than the table you are trying to insert from. In which case you should specify the variables on the target table on the insert statement (good programming practice anyway), e.g.
insert into <teradata table>(multistmt=yes)(<target variables>)
select <source variables>
from <sas table>
yes the variable is decimal 10.0 field in the Teradata. I have checked Teradata table and it has same fields/columns.
below is the ddl for the same.
CREATE MULTISET TABLE P_WDM_S_WCIS_UMD.WCIS_CLNT_DISP_IN ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP2
(
REQUEST_ID DECIMAL(10,0),
USER_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
WCIS_CLIENT_ID DECIMAL(10,0),
REQUEST_DEPT VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
REQUEST_DATE DATE FORMAT 'YYYY-MM-DD',
STATUS VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX PI_WCIS_CLNT_DISP_IN ( REQUEST_ID ,USER_ID ,WCIS_CLIENT_ID ,
REQUEST_DEPT );
> the variable is decimal 10.0 field in the Teradata
In this case why not just use
&req_id. as request_id,
Also this should be enough as well, no?
today() format = yymmddd10. as REQUEST_DATE,
I have tried that well but it did not work
Show the actual log. Make sure to use the INSERT CODE button and paste the lines into the pop-up window so that formatting is preserved.
Remember that SAS only has two data types. Fixed length character strings and floating point numbers.
If you have defined the variable as 10 decimal places in Teradata then make sure that non of the values of your data are non integers
request_id=round(request_id,1);
and that the absolute value is always less then 10,000,000,000. Otherwise Teradata will not be able to store the value.
if abs(request_id) >= 10E10 then put 'ERROR: Magnitude too large ' request_id= ;
995 | CAAPTest | 51 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 83 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 238 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 2346 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 3428 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 5901 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 7361 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 10346 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 18842 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 23466 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 24245 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 25760 | CAAPGroup | 02/28/2020 | NEW |
995 | CAAPTest | 35979 | CAAPGroup | 02/28/2020 | NEW |
It looks like you may have non printable characters somewhere.
You could clean your code by pasting it into something like notepad and back.
You could use a function like NOTPRINT or NOTGRAPH to scan the data.
I know it may sound silly, but the silly takes place often.
Another option is reducing the size of the upload until a rogue row is identified.
Lastly proc append may be faster for this task.
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.