topic date comparison in SAS 9.4 in SAS Programming

date comparison in SAS 9.4

somebody — Thu, 07 Dec 2017 23:03:35 GMT

I have a quarter variable that is in format YYQ6. I would like to do some date comparison such as if quarter = 2014Q3.

I used to be able to do it with "if date = '01Sep2014'd then ...." in SAS 9.3 but cannot do it now since I upgraded SAS.

So I am not sure if my syntax is wrong or SAS changed something with the 9.4 version. Please help!

data WORK.TEST;
  infile datalines dsd truncover;
  input TICKER:$10. CUSIP:$10. PRC:BEST12. quarter:YYQ6.;
  format PRC BEST12. quarter YYQ6.;
datalines4;
AE,00003210,6.25,1977Q1
AE,00003210,6.5,1977Q2
AE,00003210,9,1977Q3
AE,00003210,9.25,1977Q4
AE,00003210,8.875,1978Q1
AE,00003210,,1978Q2
AMFD,00016510,6.25,1983Q3
AMFD,00016510,7.5,1983Q4
AMFD,00016510,6.5625,1984Q1
AMFD,00016510,5.9375,1984Q2
;;;;

Re: date comparison in SAS 9.4

Reeza — Wed, 06 Dec 2017 23:14:49 GMT

Nothing should have affected this comparison.

Can you verify some things:

1. Is the date a SAS date with a YYQ format? It should be a numeric variable with the YYQ format. If it's a character variable then it's wrong.

2. Can you explain how it's not working and show some output to verify this? Please include your output from the following program.

data demo;
informat date yymmdd10.;
format date yyq6.;
input date;
cards;
2017/04/01
2017/10/03
2017/08/31
2017/09/01
2017/09/02
2016/01/01
2018/01/01
;
run;

data check;
set demo;
if date >= '01Sep2017'd;
run;

proc print data=demo;
proc print data=check;
run;

Re: date comparison in SAS 9.4

novinosrin — Wed, 06 Dec 2017 23:16:02 GMT

If i understand you, your quarter variable has date values formatted with yyq6. format. So I believe the underlying value doesn't change just because a format has been applied.

data w;

format d yyq6.;
d=today();
test=d<"10dec2017"d;
run;

Am i missing something here?

Re: date comparison in SAS 9.4

ballardw — Wed, 06 Dec 2017 23:18:59 GMT

Most of such "comparison" or manipulation problems result for your variable not actually being a SAS date value but a generic character variable that only looks like 2016Q3 or similar.

Run Proc Contents on your data set and see if the type is NUM and format YYQ6 or Type=Char.

If you changed how you import data from an external source or used Proc Import this likely to happen.

Re: date comparison in SAS 9.4

somebody — Wed, 06 Dec 2017 23:48:45 GMT

Output:

SAS Output

Obs date1234567

2017Q2

2017Q4

2017Q3

2016Q1

2018Q1

Obs date1234

2017Q4

2017Q3

2018Q1

Re: date comparison in SAS 9.4

somebody — Wed, 06 Dec 2017 23:49:36 GMT

this is the attribute from proc contents:

quarter

Num

YYQ6.

Re: date comparison in SAS 9.4

Reeza — Wed, 06 Dec 2017 23:55:36 GMT

Well, the query returned the expected results, so I suspect you have a data issue. Run a PROC FREQ and check if you do have data in the quarters you're looking for, perhaps your data hasn't been updated properly in the migration?

proc freq data=have;
table date;
format date yyq6.;
run;

Re: date comparison in SAS 9.4

somebody — Wed, 06 Dec 2017 23:56:12 GMT

comparison with < and > works but how about equality? Does SAS convert a specific date such as "01Sep1990"d to 1990Q3? I tried quarter="01Sep1990"d but got 0 obs. I'm pretty sure there are obs with quarter value equal 1990Q3. 😞

Re: date comparison in SAS 9.4

somebody — Wed, 06 Dec 2017 23:57:11 GMT

Does SAS convert a specific date such as "01Sep1990"d to 1990Q3? I tried quarter="01Sep1990"d but got 0 obs. I'm pretty sure there are obs with quarter value equal 1990Q3. 😞

Re: date comparison in SAS 9.4

Reeza — Thu, 07 Dec 2017 00:04:38 GMT

No, it doesn't convert it, you would need to convert it with a PUT statement.

@somebody wrote:

Does SAS convert a specific date such as "01Sep1990"d to 1990Q3? I tried quarter="01Sep1990"d but got 0 obs. I'm pretty sure there are obs with quarter value equal 1990Q3. 😞

Re: date comparison in SAS 9.4

somebody — Thu, 07 Dec 2017 00:09:18 GMT

how would I do that? how would I choose obs that has the quarter value of 1990Q3?

Re: date comparison in SAS 9.4

Reeza — Thu, 07 Dec 2017 00:13:43 GMT

Use a PUT() to convert it to a character variable.

Or use YEAR() and QTR() to get the year and quarter and compare those.

Re: date comparison in SAS 9.4

ballardw — Thu, 07 Dec 2017 16:20:28 GMT

@somebody wrote:

Output:

SAS Output

Obs date1234567

2017Q2

2017Q4

2017Q3

2017Q3

2017Q3

2016Q1

2018Q1

Obs date1234

2017Q4

2017Q3

2017Q3

2018Q1

Useless.

That does not even tell us if the variable is character or not.

Example data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

10 observations of records with the example variables should clear up many questions.

Re: date comparison in SAS 9.4

Reeza — Thu, 07 Dec 2017 16:28:36 GMT

@ballardw That's the output of the code I posted that he posted. It shows that SAS is working properly so his issue is with his code and data. The question seems to have changed so I believe the original issue is likely resolved now.

Re: date comparison in SAS 9.4

somebody — Thu, 07 Dec 2017 23:05:35 GMT

I have edited the question. But it does not look right as the value for quarter is in the formated form i.e. 1997Q3. I calculated this by converting from a date variable 19970901.

Re: date comparison in SAS 9.4

somebody — Fri, 08 Dec 2017 07:11:55 GMT

I only changed from < and > comparison to = and added some extra info. I got it to work when comparing < and > using '01Sep2014'd but cannot use this format when checking equality

Re: date comparison in SAS 9.4

RRum — Mon, 13 Aug 2018 15:31:34 GMT

if year(date)=1990 and qtr(date)=3;