BookmarkSubscribeRSS Feed
Rohit12
Obsidian | Level 7

 

I am trying to delete the data from customer table where start_date is in datetime .It has values like

 

Start_date
2017-09-01 00:00:00.000
2017-09-01 00:00:00.000
2017-09-01 00:00:00.000
2017-09-01 00:00:00.000

 


%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;
delete from LZZ.customer
where start_date=1819843200;
QUIT;




%mend;

 

Below is the error that is coming 

 

ERROR: Open cursor error: ICommand::Execute failed. : The requested properties cannot be supported.

 

Thanks!!

7 REPLIES 7
cpagrawal
Fluorite | Level 6

The Error you have reported is coming when you are comipiling the macro or after calling the macro ?? 
if possible can you please provide full log for the same ? 

Rohit12
Obsidian | Level 7

Now I have tried this code but it is not woking 

 

%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;
delete from LZZ.customer
where START_DATO='2017-09-01 00:00:00.000';
QUIT;




%mend;


OLEDB: AUTOCOMMIT turned ON for connection id 4 771 1819876483 Main 0 OBJECT_E
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 4 772 1819876483 Main 0 OBJECT_E
OLEDB: AUTOCOMMIT turned OFF for connection id 4 773 1819876483 Main 0 OBJECT_E
NOTE: Libref LZZ was successfully assigned as follows:
Engine: OLEDB
Physical Name: SQLOLEDB

OLEDB: AUTOCOMMIT turned ON for connection id 5 774 1819876483 Main 0 SQL (2)
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 5 775 1819876483 Main 0 SQL (2)
OLEDB: AUTOCOMMIT turned OFF for connection id 5 776 1819876483 Main 0 SQL (2)
OLEDB: AUTOCOMMIT turned ON for connection id 5 777 1819876483 Main 0 SQL (2)
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 5 778 1819876483 Main 0 SQL (2)
779 1819876483 Main 0 SQL (2)
OLEDB_47: Prepared: on connection 5 780 1819876483 Main 0 SQL (2)
SELECT * FROM "LZS"."customer" 781 1819876483 Main 0 SQL (2)
782 1819876483 Main 0 SQL (2)
ERROR: Expression using equals (=) has components that are of different data types.
OLEDB: AUTOCOMMIT turned ON for connection id 5 783 1819876483 Main 0 SQL (2)
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 5 784 1819876483 Main 0 SQL (2)
OLEDB: AUTOCOMMIT turned OFF for connection id 5 785 1819876483 Main 0 SQL (2)
SAS_SQL: The statement cannot be processed directly by the database because the DBIDIRECTEXEC option
was not specified. This option replaces the DIRECT_EXE option for which support will be dropped in
the future. 786 1819876483 Main 0 SQL (2)
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. 787
1819876483 Main 0 SQL (2)

OLEDB: AUTOCOMMIT turned ON for connection id 6 788 1819876483 Main 0 SQL (2)
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 6 789 1819876483 Main 0 SQL (2)
OLEDB: AUTOCOMMIT turned OFF for connection id 6 790 1819876483 Main 0 SQL (2)
791 1819876483 Main 0 SQL (2)
OLEDB_48: Prepared: on connection 6 792 1819876483 Main 0 SQL (2)
SELECT * FROM "LZS"."customer" 793 1819876483 Main 0 SQL (2)
794 1819876483 Main 0 SQL (2)
ERROR: Expression using equals (=) has components that are of different data types.
OLEDB: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-* on connection 6 795 1819876483 Main 0 SQL (2)
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.18 seconds
cpu time 0.01 seconds

Oligolas
Barite | Level 11

The answer is in the log

ERROR: Expression using equals (=) has components that are of different data types.
________________________

- Cheers -

Rohit12
Obsidian | Level 7
How can i remove this error and what is the solution
Oligolas
Barite | Level 11

Hi,

 

assuming start_date is character you could try

PROC SQL;
   CREATE TABLE test AS
      SELECT * 
      FROM LZZ.customer
   ;
   DELETE FROM test
   WHERE start_date EQ translate(put(1819843200,E8601dt23.3),' ','T')
   ;
QUIT;
________________________

- Cheers -

Patrick
Opal | Level 21

@Rohit12

I should start to save away a standard answer/explanation for this issue as it comes up at least once a month. 

 

So what happens here:

You're using implicit SQL (=SAS flavour SQL) for execution in a database. The SAS/Access engine will translate this SAS SQL to the database flavour SQL and send it to the database for execution.

What you need to do is to pass in your datetime value in a form so that SAS recognizes that this is a datetime value and can translate the string to something the database understands as a datetime value.

 

The way you're passing in the string in the moment:

where START_DATO='01Sep2017:00:00:00.000'dt

doesn't tell SAS that this is a string representing a datetime value. For SAS that's just a string (a sequence of characters). That's why you're getting the error:

ERROR: Expression using equals (=) has components that are of different data types.

The following should work as it provides the instruction to SAS that it's dealing with a string that needs to be interpreted as a datetime value. 

where START_DATO='01Sep2017:00:00:00.000'dt

 

ChrisBrooks
Ammonite | Level 13

I'm not sure what RDBMS you're using but I have a feeling it might be related to the fact that you have CURSOR_TYPE=STATIC in your libname declaration. Try removing that and just going with the default.

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
  • 7 replies
  • 1863 views
  • 0 likes
  • 5 in conversation