topic Re: Proc SQL Where Clause with Date in SAS Programming

Proc SQL Where Clause with Date

SaschaD — Wed, 22 Jul 2020 15:08:02 GMT

Hi,

I tried to add to my where clause a calculated date like Today()-1, but I got always the error that the datatypes differs.

The format from the salesday are YYYYMMDD and numeric.

PROC SQL;
	CREATE TABLE WORK.NG_Export AS
		SELECT
			T1.SAS_ID__c,
			T1.Salesday__c,
		FROM APPG.KOPIE_V_APP_NG T1
		WHERE 
			T1.Salesday__c = PUT(INTNX('Day', TODAY(), -1, 'SAME'), yymmddn8.);

QUIT;

Could you help me plase.

Thanks,

Sascha

Re: Proc SQL Where Clause with Date

PaigeMiller — Wed, 22 Jul 2020 15:11:29 GMT

This line:

T1.Salesday__c = PUT(INTNX('Day', TODAY(), -1, 'SAME'), yymmddn8.)

T1.Salesday__c is numeric, PUT(...) is character, you can't have that, you can only compare a numeric to a numeric.

Since I don't have your data, it may be (or may not be) that this works

T1.Salesday__c = INTNX('Day', TODAY(), -1, 'SAME')

Re: Proc SQL Where Clause with Date

SaschaD — Thu, 23 Jul 2020 05:59:08 GMT

Thanks for your response.

But it doesn't work, because the value of

INTNX('Day', TODAY(), -1, 'SAME')

is 22118.

But I need the format YYYYMMDD like 20200722.

The values of salesday also have this format.

Re: Proc SQL Where Clause with Date

ChrisNZ — Thu, 23 Jul 2020 06:10:02 GMT

The format is just the way the value is displayed.

The test uses the actual value, regardless of the format. otherwise two dates formatted differently wouldn't match, which is silly.

Also, the parameter 'same' has no effect here and can be removed.

Re: Proc SQL Where Clause with Date

SaschaD — Thu, 23 Jul 2020 06:17:15 GMT

Ok, but what should I do now?

The result is the same, I got the value 22118.

The structure of today should be 20200722 numeric, how can I convert the date to this format?

Re: Proc SQL Where Clause with Date

ChrisNZ — Thu, 23 Jul 2020 06:28:28 GMT

Are you saying that variable SALESDAY__C is numeric with value 20200722 ?

That's a silly way to code dates. Change this if you can.

For that value, you can compare two strings:

where put(T1.SALESDAY__C,8.) = put(intnx('day', today(), -1) , yymmddn8.);

Re: Proc SQL Where Clause with Date

SaschaD — Thu, 23 Jul 2020 06:31:35 GMT

I tried that, but I got this message:
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.

Re: Proc SQL Where Clause with Date

ChrisNZ — Thu, 23 Jul 2020 06:33:46 GMT

Probably too many semicolons.

Re: Proc SQL Where Clause with Date

SaschaD — Thu, 23 Jul 2020 06:36:27 GMT

Yeah, you are right, Thanks. My fault.