topic deleting the data in SAS Programming

deleting the data

Rohit12 — Fri, 01 Sep 2017 06:13:43 GMT

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!!

Re: deleting the data

cpagrawal — Fri, 01 Sep 2017 07:50:51 GMT

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 ?

Re: deleting the data

Rohit12 — Fri, 01 Sep 2017 09:25:09 GMT

Now I have tried this code but it is not woking

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

Re: deleting the data

ChrisBrooks — Fri, 01 Sep 2017 09:26:16 GMT

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.

Re: deleting the data

Oligolas — Fri, 01 Sep 2017 09:33:35 GMT

The answer is in the log

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

Re: deleting the data

Rohit12 — Fri, 01 Sep 2017 09:40:58 GMT

How can i remove this error and what is the solution

Re: deleting the data

Oligolas — Fri, 01 Sep 2017 11:09:52 GMT

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;

Re: deleting the data

Patrick — Fri, 01 Sep 2017 11:31:37 GMT

@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