DATA Step, Macro, Functions and more

date comparison in SAS 9.4

Reply
Contributor
Posts: 61

date comparison in SAS 9.4

[ Edited ]

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
;;;;
Super User
Posts: 23,776

Re: date comparison in SAS 9.4

Posted in reply to ducman1611

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;
Contributor
Posts: 61

Re: date comparison in SAS 9.4

Output:

SAS Output



Obs date1234567
2017Q2
2017Q4
2017Q3
2017Q3
2017Q3
2016Q1
2018Q1

 



Obs date1234
2017Q4
2017Q3
2017Q3
2018Q1
Super User
Posts: 13,583

Re: date comparison in SAS 9.4

Posted in reply to ducman1611

ducman1611 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-dat... 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.

Super User
Posts: 23,776

Re: date comparison in SAS 9.4

@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. 

Contributor
Posts: 61

Re: date comparison in SAS 9.4

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
Contributor
Posts: 61

Re: date comparison in SAS 9.4

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. 

 

PROC Star
Posts: 1,836

Re: date comparison in SAS 9.4

Posted in reply to ducman1611

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?

Contributor
Posts: 61

Re: date comparison in SAS 9.4

Posted in reply to novinosrin

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. :-(

Super User
Posts: 13,583

Re: date comparison in SAS 9.4

Posted in reply to ducman1611

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.

Contributor
Posts: 61

Re: date comparison in SAS 9.4

this is the attribute from proc contents:

quarterNum8YYQ6.
Super User
Posts: 23,776

Re: date comparison in SAS 9.4

Posted in reply to ducman1611

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;
Contributor
Posts: 61

Re: date comparison in SAS 9.4

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. :-(

Super User
Posts: 23,776

Re: date comparison in SAS 9.4

[ Edited ]
Posted in reply to ducman1611

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

 


ducman1611 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. :-(


 

 

Contributor
Posts: 61

Re: date comparison in SAS 9.4

how would I do that? how would I choose obs that has the quarter value of 1990Q3?
Ask a Question
Discussion stats
  • 15 replies
  • 274 views
  • 0 likes
  • 4 in conversation