CODEWITHSUNDEEP
sqlsas
blidt
blidt

Reputation: 129

How can i get the last day of the month given a date?

I am trying to get the number of records from a table but only when the date is the last day of the month. I am connecting to teradata and trying the following:

PROC SQL;
    CREATE TABLE A AS SELECT DISTINCT DATE, COUNT(*)
        FROM TERADATA_LIB.SOME_TABLE
            WHERE DATE = intnx('month', DATE,0, 'E');
QUIT;

but apparently the intx function is not valid inside PROC SQL.

How else can i get this done?

Thanks.

Upvotes: 0

Views: 840

Answers (1)

Richard
Richard

Reputation: 27516

Is the apparentness from a LOG message, or the fact that your result set is not as you expect?

The DATE variable in the Teradata table could be a datetime value.

Try DATEPART with the INTNX('MONTH' or use datetime intervals DTMONTH and DTDAY. Also, is the SQL example correct? You select DATE, COUNT(*) but do not have a GROUP clause. Do you want SAS to automatically remerge the summary statistic COUNT to every record in the result set?

Example:

Generate hourly datetime values for 8 months. 8*24=192 records will occur on the last day of a month.

data have(label='5833 hourly records, expect 8*24=192 records with last day of month');
  do date = '01-JAN-2022T0:0:0'dt to '01-SEP-2022T0:0:0'DT by '01:00't;
    seq + 1;
    output;
  end;
run;

* criteria uses DATEPART and MONTH interval;
proc sql;
  create table want(label='How many records are dated for the last day of a month') as 
  select 
    count(*) as count
  from 
    have
  where
    datepart(date) = intnx('month',datepart(date),0,'E')
  ;

* criteria uses datetime value and DT intervals DTMONTH and DTDAY;
proc sql;
  create table want(label='How many records are dated for the last day of a month') as 
  select 
    count(*) as count
  from 
    have
  where
    intnx('DTMONTH',date,0,'E') = intnx('DTDAY', date, 0, 'E')
  ;

Upvotes: 1

Related Questions