Proc SQL Where Clause with Date

[SaschaD]

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

[PaigeMiller]

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')

[SaschaD]

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.

[ChrisNZ]

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.

[SaschaD]

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?

[ChrisNZ · Accepted solution]

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.);

[SaschaD]

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

[ChrisNZ]

Probably too many semicolons.

[SaschaD]

Yeah, you are right, Thanks. My fault.