topic Re: Date comparison in New SAS User

Date comparison

jorquec — Fri, 06 Sep 2019 15:36:11 GMT

Hi,

Could someone give some help?

I guess this piece of code is not correct to compare data I guess is that they are in different formats.

The fisrt step generates a table with this results

a.maxmargin_dt = 31JUL2019 and b.maxmodelmart_d= 355

And macro data generates ONEDT2 = 1190831 and month_id = 356

IF (maxmargin_dt >(&ONEDT2.)/100 ) and (maxmodelmart_d <> &month_id.) then export_flag='Y'; else export_flag='N';

This should result in export_flag = N because only the second conditions is true , however it results me as Y , so I guess despite of I had use (maxmargin_dt >(&ONEDT2.)/100 ) this converstion it is not working.

Any help will be apprecciate I am not an expert in date and I have to use macro data functions because this is an automatic routine process.

Kindly regards,

%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());

data _null_;

date2=intnx("month",&today.,-1,'end');
call symput('ONEDT2',"1"||substr(put(date2,DDMMYYN.),7,2)||substr(put(date2,DDMMYYN.),3,2)||substr(put(date2,DDMMYYN.),1,2));
%put &ONEDT2.;
%put ONEDT2=&ONEDT2.;


month_id = intck('month','01jan1990'd,today());
put month_id=;
call symputx('month_id',month_id);
%put month_id=&month_id.;

run;

/*STEP 1 */
proc sql;
 drop table IRM.TESTPAYG;
quit;

Proc SQL;
connect to teradata 
(user=&teradata_user. password=&teradata_pwd. server = 'edwprod' database = 'nuc_pl_user_view');  
Create  table IRM.TESTPAYG as select * from connection to teradata(

 select a.ID,  a.maxmargin_dt, b.maxmodelmart_d from 
(  
  select  
			1 as ID,
 			max(month_end_dt) as maxmargin_dt
from nuc_pl_user_view.pg_margin_stack) as A
left join (
select  
			1 as ID,
			max(month_id) as maxmodelmart_d
 from  Insights_rm.Consumer_Model_Mart) as B
on a.ID = b.ID

 );
disconnect from teradata ;
QUIT;

/* STEP 2 */

proc sql;
 drop table IRM.TESTPAYG2;
quit;

DATA IRM.TESTPAYG2;
SET IRM.TESTPAYG;
IF  (maxmargin_dt >(&ONEDT2.)/100 ) and (maxmodelmart_d <> &month_id.) then export_flag='Y'; 

else export_flag='N';

call symputx('export_flag', export_flag);

run;

Re: Date comparison

PaigeMiller — Fri, 06 Sep 2019 16:51:53 GMT

@jorquec wrote:

Hi,

Could someone give some help?

I guess this piece of code is not correct to compare data I guess is that they are in different formats.

The fisrt step generates a table with this results

a.maxmargin_dt = 31JUL2019 and b.maxmodelmart_d= 355

And macro data generates ONEDT2 = 1190831 and month_id = 356

IF (maxmargin_dt >(&ONEDT2.)/100 ) and (maxmodelmart_d <> &month_id.) then export_flag='Y'; else export_flag='N';

This should result in export_flag = N because only the second conditions is true , however it results me as Y , so I guess despite of I had use (maxmargin_dt >(&ONEDT2.)/100 ) this converstion it is not working.
 

Is maxmargin_dt the text sting 31JUL2019 ??? Or does it contain a numeric value which is formatted to appear as 31JUL2019.

Is it character or numeric. Please look this up and let us know.

Re: Date comparison

jorquec — Mon, 09 Sep 2019 08:24:39 GMT

Hi,

The source table which contain this variable show me that

max(month_end_dt) as maxmargin_dt is a column on a date format.

So I don't understand why the comparison is not working. Could you please give me some help?

Re: Date comparison

ballardw — Mon, 09 Sep 2019 16:38:09 GMT

@jorquec wrote:

Hi,

The source table which contain this variable show me that

max(month_end_dt) as maxmargin_dt is a column on a date format.

So I don't understand why the comparison is not working. Could you please give me some help?

Comparisons with literal dates require the value to be in quotes with a trailing D to indicate the value is a date:

mydatevariable ge '01Jan2019'd

for example.

Similarly times are end in t "12:15:30"t and datetimes in dt '01Jan2019:12:15:30'dt.

Single or double quotes and upper or lowercase are acceptable.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates.

Re: Date comparison

PaigeMiller — Mon, 09 Sep 2019 16:40:28 GMT

@jorquec wrote:

Hi,

The source table which contain this variable show me that

max(month_end_dt) as maxmargin_dt is a column on a date format.

So I don't understand why the comparison is not working. Could you please give me some help?

Be specific. What exact date format?

Re: Date comparison

jorquec — Mon, 09 Sep 2019 17:00:14 GMT

Sorry the owner table just said to me that it is a DATE , I have check some and it shows me

30/06/2019

31/12/2014

30/05/2015

Re: Date comparison

jorquec — Mon, 09 Sep 2019 16:59:11 GMT

I have made a print screen and it shows me :

30/06/2019

30/05/2019

31/12/2014

Re: Date comparison

PaigeMiller — Mon, 09 Sep 2019 17:06:27 GMT

And how do you know these are really numbers formatted to look like 30/06/2019? How do you know these are not character strings? Please look at the PROC CONTENTS of the data set IRM.TESTPAYG2 to see if it is numeric or character, and what the exact format is, so that we get exact information, instead of this guessing?

Re: Date comparison

jorquec — Tue, 10 Sep 2019 08:23:01 GMT

Hi,

Thanks for your help. Please see attached proc contents with formats.

Re: Date comparison

PaigeMiller — Tue, 10 Sep 2019 12:09:00 GMT

Some of us (including me) will not or cannot download and open Excel files (or other Microsoft Office documents) because they are a security threat.

Please copy the PROC CONTENTS output as text, and paste it into the window that appears when you click on the {i} icon. This preserves the formatting of the output and makes it much more readable and understandable.

Re: Date comparison

jorquec — Tue, 10 Sep 2019 12:21:21 GMT



The CONTENTS Procedure						
Data Set Name	IRM.TESTPAYG2	Observations	.			
Member Type	DATA	Variables	4			
Engine	TERADATA	Indexes	0			
Created	.	Observation Length	0			
Last Modified	.	Deleted Observations	0			
Protection		Compressed	NO			
Data Set Type		Sorted	NO			
Label						
Data Representation	Default					
Encoding	Default					
Alphabetic List of Variables and Attributes						
#	Variable	Type	Len	Format	Informat	Label
1	ID	Num	8	6	6	ID
4	export_flag	Char	1	$1.	$1.	export_flag
2	maxmargin_dt	Num	8	DATE9.	DATE9.	maxmargin_dt
3	maxmodelmart_d	Num	8	12	12	maxmodelmart_d

Hope you can see this way, sorry I didn't know about restriction on attachments.

Re: Date comparison

PaigeMiller — Tue, 10 Sep 2019 12:44:38 GMT

call symput('ONEDT2',"1"||substr(put(date2,DDMMYYN.),7,2)||substr(put(date2,DDMMYYN.),3,2)||substr(put(date2,DDMMYYN.),1,2));

I don't know what this is supposed to do, or why you need the "1" at the beginning of the string, but this does NOT create a SAS date value. Nevertheless, later when you want to compare &ONEDT2 (which is not a SAS date) to a SAS date in this line

IF  (maxmargin_dt >(&ONEDT2.)/100 )

the comparison will fail, this is not a meaningful comparison to compare MAXMARGIN_DT (which is a SAS date) with &ONEDT/100 which is not a SAS date (and why divide by 100?)

If you want to compare the dates, then you need a SAS date value (or the equivalent integer), followed by ">" followed by another SAS date value (or the equivalent integer).

For example, in your DATA _NULL_ step, use this to create a macro variable named &DATE2 based upon the value of data set variable date2. This is a valid SAS date value, because that's how it was created.

call symputx('date2',date2);

Then later in your code, use

IF  (maxmargin_dt > &date2 ) ...

Re: Date comparison

jorquec — Tue, 10 Sep 2019 13:07:17 GMT

Hi,

Thanks for your code I am testing but I would like to know if there is a way to see date2 in another format just to check in put statement, because I can not understand "21792 " .

Thanks again.

data _null_;
35
36 date2=intnx("month",&today.,-1,'end');
37 call symputx('date2',date2);
38 %put date2=&date2.;
date2=21792
39

Re: Date comparison

PaigeMiller — Tue, 10 Sep 2019 13:14:19 GMT

SAS date values are integers, representing the number of days since January 1, 1960.

To see what 21792 represents, try this:

%put &=date2;
%put DATE2 = %sysfunc(putn(&date2,date9.));

Re: Date comparison

jorquec — Tue, 10 Sep 2019 13:22:24 GMT

Hi PaigeMiller

Many thanks for your patience , for your help. You are absolutely great !!!

Sorry for my basic knowledge some of code that I share were not written by me so I just received a lot of old stuffs and I am trying to do my best which is really hard sometimes as a beginner.

Again many many thanks.

Kindly regards.