BookmarkSubscribeRSS Feed
Rohit12
Obsidian | Level 7

 

I am trying to insert a data in SQL server table using SAS 

 

Below is the code that I have written 

 

%macro lzlib;


LIBNAME LZZ OLEDB BULKLOAD=YES
DBCOMMIT=0
CURSOR_TYPE=STATIC
BL_KEEPIDENTITY=YES
BL_KEEPNULLS=NO
DIRECT_EXE=DELETE
PROPERTIES=('Integrated Security'=SSPI
'Persist Security Info'=TRUE
'Initial Catalog'=Customer_account)
PROMPT=NO
REREAD_EXPOSURE=NO
PROVIDER=SQLOLEDB
DATASOURCE="dwh-i-sql"
SCHEMA=DZS
ROWSET_SIZE=32767
UPDATE_MULT_ROWS=YES
PRESERVE_GUID=yes;


PROC SQL;
INSERT INTO LZZ.CUST_DIM
SELECT DATO
,JOBNAME
,RUNDATE
,EXECUTION_STATUS
,COMPARE
,LcOMPARE_RESULT
,OUTPUT_FLAT_FILE_EXISTS
,FOLDER_LINK
,FILE_NAME
,ADD_INFO
,FILE_EXISTS
,HOST_FILE_DOWNLOAD
,FILE_READ
,HOST_FILE_READ
,COMPARE_PROCEDURE
,COMPARE_STATUS
,BASEDATASETOBS
,COMPAREDATASETOBS
,BASEDATASETVAR
,COMPAREDATASETVAR FROM FINAL;
QUIT;


%mend;

 

This is the error that comes up 

 

ERROR: Execute error: IRowsetFastLoad::InsertRow failed. : Unspecified error

ERROR: ROLLBACK issued due to errors for data set LZZ.CUST_DIM.DATA.

 

 

Thanks!!

 

4 REPLIES 4
LinusH
Tourmaline | Level 20

Try

options msglevel=i sastrace=',,,d' sastraceloc=saslog;

Might give you some more info in the log.

Check the data and try to see if they break any constraints, or if you have any read only columns.

Check in SQL Server logs, enable additional audit if necessary.

Data never sleeps
Rohit12
Obsidian | Level 7
All the constraints are fine .I am not able to undertsand what the error is
Tom
Super User Tom
Super User

Most likely you have a type conflict for one of the column or perhaps just one of the values for one of the columns.

 

Find an example observation that it cannot insert.

Then repeatally try loading the row with different combinations of variables selected to see which variable(s) are causing the problem.

 

You should probably check the date varaibles first. SAS stores DATE and DATETIME values differently, but many DBMS have only one data type for dates.  You might need to convert your SAS DATE value to a DATETIME value to put it into the target variable.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2942 views
  • 0 likes
  • 4 in conversation