BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SaschaD
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

 

 

 

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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

 

 

 

--
Paige Miller
SaschaD
Obsidian | Level 7

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
Tourmaline | Level 20

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
Obsidian | Level 7

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
Tourmaline | Level 20

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
Obsidian | Level 7
I tried that, but I got this message:
WARNING: This SAS global statement is not supported in PROC SQL. It has been ignored.
ChrisNZ
Tourmaline | Level 20
SaschaD
Obsidian | Level 7
Yeah, you are right, Thanks. My fault.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 8 replies
  • 2995 views
  • 0 likes
  • 3 in conversation