where date

[Thalitacosta]

I have a variable "date" in the format "jun20" (MONYY5.). I'm creating a new table, doing some calculations per month, but I can"t select just for months. What to do?

proc sql;

create table calc2 as

select DATE,

'00'n,

no_default,

default,

total,

sum(default) as total_no_d,

sum(no_default) as total_def,

default / sum(default) as dist_no_d,

no_default / sum(no_default) as dist_def

from calc

where DATE = 'JUN20';

quit;

[PaigeMiller · Accepted solution]

Is DATE numeric or character (text)? Please run PROC CONTENTS on data set CALC and show us what it says for variable DATE.

[Thalitacosta]

type: numeric

length: 4

group: date

format: MONYY5.

Informat: YYMMDD8.

[PaigeMiller]

The key thing to remember is that when doing Boolean comparisons, such as in words, the month is equal to June 2020, the format is irrelevant. What matters is the internal representation of the date, which is an integer indicating the number of days since 01JAN1960. Now, I suspect you (like 100% of everybody else) don't know how many days that is, so SAS provides a syntax for representing June 1, 2020, and that is '01JUN2020'd. This allows humans (who think in days months and years) to communicate with SAS, which thinks in terms of the number of days since 01JAN1960.

So, what you want to program is this:

where DATE between '01JUN2020'd and '30JUN2020'd;

[Kurt_Bremser]

SQL uses raw (unformatted) values for the condition, so you need to adapt for that. I guess(!) that you want the year 2020, not 1920 or so (insert usual comment about the intelligence (or lack thereof) of using 2-digit years).

So change your condition to this:

where put(DATE,yymmn6.) = "202006"

or, other way round

where date = '01jun2020'd

if these are typical "monthly dates" where it is always the first.