SAS date comparison failure in SAS 9.2

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

SAS date comparison failure in SAS 9.2

Hello! Need your help.

I have to compare Dates, and based on Date assign data to 2 different data sets (using SAS 9.2).

Here is what I have:

%Let EffectiveDate = %sysfunc(compress(&NewPrice,,kd)); *Determine effective date of the most recent rate;

%put &effectiveDate;

 

No problem here, program prints for me EffectiveDate say 011517;

Then I read data set with original Dates as

Date1 :$10  from some text tab delimetered file. No problem.

I convert this Date1 to Date to match format  of EffectiveDate:

Date2=input(Date1, mmddyy10.);

Date= put(Date2, mmddyy6.);

 

Now I need to divide data based on Date into 2 data sets:

 

data A B;

set abc;

ChRateDay = input("&EffectiveDate",mmddyy6.);

 if Date < ChRateDay then output A;

else output B;

run;

 

I get all data in set A and nothing in set B. There are no errors in the log.

Original data set has data both prior and past "effective date"

What am I doing wrong?

Thanks.

 


Accepted Solutions
Solution
‎02-07-2017 06:57 PM
PROC Star
Posts: 7,363

Re: SAS date comparison failure in SAS 9.2

From your description it's hard to tell what you are actually working with, but the following seems to mimic what you said, and produces the desired result:

 

%let EffectiveDate=011517;

data abc;
  input date $10.;
  cards;
052516
020717
;
data A B;
  set abc;
  if input(Date,mmddyy6.) < input("&EffectiveDate.",mmddyy6.) then output A;
  else output B;
run;

HTH,

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎02-07-2017 06:57 PM
PROC Star
Posts: 7,363

Re: SAS date comparison failure in SAS 9.2

From your description it's hard to tell what you are actually working with, but the following seems to mimic what you said, and produces the desired result:

 

%let EffectiveDate=011517;

data abc;
  input date $10.;
  cards;
052516
020717
;
data A B;
  set abc;
  if input(Date,mmddyy6.) < input("&EffectiveDate.",mmddyy6.) then output A;
  else output B;
run;

HTH,

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 10

Re: SAS date comparison failure in SAS 9.2

Thanks a lot!
It worked this way.

I guess problem was that I did not use input for Date in comparision.

Super User
Posts: 17,831

Re: SAS date comparison failure in SAS 9.2

Your dates are character strings and the comparisons won't work. If you use YYMMDD then it will sort correctly. 

 

Or you can use SAS dates, which are numeric and then apply the relevant formats. This is the solution Art has proposed and probably the best. 

Super User
Posts: 10,500

Re: SAS date comparison failure in SAS 9.2

Did you see any notes about character values being converted to numeric or numeric to character in the log?

Occasional Contributor
Posts: 10

Re: SAS date comparison failure in SAS 9.2

Yew, there was some character conversion, and I thought it was related to data.

Super User
Posts: 3,108

Re: SAS date comparison failure in SAS 9.2

Such notes should be fixed by correcting the data types of the variables involved - while @art297's fix works you shouldn't need to convert date variables to compare them. Storing them as SAS dates to begin with would be considered best practice and will simplify your coding.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 112 views
  • 0 likes
  • 5 in conversation