topic Re: SAS Dates in SAS Programming

SAS Dates

wisej — Tue, 06 Apr 2021 14:56:17 GMT

Hi,

I have a date variable formatted as yyddmm (ex. 2021-04-06). I need to run descriptive statistics on data collected last month. Any advice on how to do this if the date variable is formatted this way? I appreciate any help!

Re: SAS Dates

Reeza — Tue, 06 Apr 2021 15:01:05 GMT

By last month do you mean March 2021 or do you mean whenever you run the code it should run for the prior month?

Assuming your variable is called dateVariable and is a SAS date, ie numeric with a date format you can filter your data with a WHERE statement.

where intnx('month', dateVariable, 0, 'b') = '01Mar2021'd;

Or dynamically:

where intnx('month', today(), -1, 'b') = intnx('month', dateVariable, 0, 'b');

@wisej wrote:

Hi,

I have a date variable formatted as yyddmm (ex. 2021-04-06). I need to run descriptive statistics on data collected last month. Any advice on how to do this if the date variable is formatted this way? I appreciate any help!

Re: SAS Dates

Reeza — Tue, 06 Apr 2021 15:03:47 GMT

And a yyddmm is very, very rare, it's almost always yymmdd so I would confirm that.
SAS doesn't have a format for that date format AFAIK .....

Re: SAS Dates

PaigeMiller — Tue, 06 Apr 2021 15:16:05 GMT

@wisej wrote:

Hi,

I have a date variable formatted as yyddmm (ex. 2021-04-06). I need to run descriptive statistics on data collected last month. Any advice on how to do this if the date variable is formatted this way? I appreciate any help!

Assign a format to the date variable, for example yymm. and then PROC MEANS and almost all other PROCs that do descriptive statistics will provide you with the statistics for each month in the data set.

Re: SAS Dates

wisej — Tue, 06 Apr 2021 16:00:54 GMT

Thanks for replying! Every time I run the code I would like it to run for the prior month.

Re: SAS Dates

wisej — Tue, 06 Apr 2021 16:06:45 GMT

Can you please explain the syntax of the where statement you provided? Thanks!

Re: SAS Dates

Reeza — Tue, 06 Apr 2021 16:59:24 GMT

INTNX increments dates, the first parameter specifies the interval, month. The second is the date variable and the third is the increment, 0 is same month, -1 is prior month, 1 is next month.
The last, says to align it to the beginning of the month. TODAY() returns the current date so it would convert that date to the start of the prior month and check if that matches the date variable which has been aligned to the beginning of the month.

An alternative would be to compare the year and month components but that's more work IMO.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lefunctionsref&docsetTarget=p10v3sa3i4kfxfn1sovhi5xzxh8n.htm&locale=en

Re: SAS Dates

Sajid01 — Tue, 06 Apr 2021 23:16:00 GMT

Please have a look at the sample code and data. This run proc univariate on previous months data; Hope this will solve your problem.
I have taken aa sample data to show that it works.

data test;
informat date1 yymmdd10.;
format date1  yymmdd10.;
input date1 somevar;
datalines;
2021-02-06 15
2021-02-06 11
2021-03-01 09
2021-03-10 12
2021-03-16 14
2021-03-19 16
2021-03-25 08
2021-03-29 03
2021-04-06 12
2021-04-06 21
;
run;

%let last_month = %sysfunc(month(%sysfunc(intnx(month,%sysfunc(date()),-1))));

proc univariate data=test(where=(month(date1)=&last_month.));
var somevar;
run;

Re: SAS Dates

Reeza — Wed, 07 Apr 2021 02:37:07 GMT

What if the years are different? What if they have 2020 data in the data set as well?

Re: SAS Dates

Tom — Wed, 07 Apr 2021 04:40:49 GMT

@wisej wrote:

Hi,

I have a date variable formatted as yyddmm (ex. 2021-04-06). I need to run descriptive statistics on data collected last month. Any advice on how to do this if the date variable is formatted this way? I appreciate any help!

The word FORMAT has a special meaning in SAS. A format is instructions for how to display values as text. SAS does not have any date format that would display dates in YDM order. It does have some, like YYMMDD, that will display dates in YMD order.

For purposes of providing an answer lets assume that there is a variable named DATE that contains date values (number of days since 1960). Most likely if it display as string like '2021-04-06' then it has the format specification YYMMDD10. attached to it (although there are other date specifications that would display dates in that same style).

To test for last month you need to know today's month. You can use the DATE() function (or its alias name of TODAY()) to get today's date. Then you can move back one month to find last month by using the INTNX() function with the interval type of month and and offset of negative one.

intnx('month',today(),-1)

then you can test if the value is in last month in a number of ways.

You could convert DATE to the same date and compare:

where intnx('month',DATE,-1) = intnx('month',today(),-1) ;

You could convert both the strings using a format that only displays the YEAR and MONTH part of the date and compare those strings.

where put(DATE,monyy7.) = put(intnx('month',today(),-1),monyy7.) ;

Or you could test if the date is in the interval defined by the first and last day of the month. That might work faster if the source data is indexed on DATE.

where date between intnx('month',today(),-1,'b') and intnx('month',today(),-1,'e');

Re: SAS Dates

wisej — Wed, 07 Apr 2021 17:46:30 GMT

The dataset does contain data from 2019 and 2020 as well.

Re: SAS Dates

Sajid01 — Sun, 11 Apr 2021 15:38:14 GMT

This takes care of both month and year;

data test;
informat date1 yymmdd10.;
format date1  yymmdd10.;
input date1 somevar;
datalines;
2020-03-01 09
2020-03-10 12
2020-03-16 14
2020-03-19 16
2020-03-25 08
2021-02-06 15
2021-02-06 11
2021-03-01 09
2021-03-10 12
2021-03-16 14
2021-03-19 16
2021-03-25 08
2021-03-29 03
2021-04-06 12
2021-04-06 21
;
run;

proc univariate data=test(where=(put(date1,monyy7.)="%sysfunc(intnx(month,%sysfunc(date()),-1),monyy7.)"));
var somevar;
run;

From the result we see that there were six observations in the analysis.
The original dataset too has six observations for last month (Mar2021)


Moments
N	6	Sum Weights	6
Mean	10.3333333	Sum Observations	62
Std Deviation	4.67618078	Variance	21.8666667
Skewness	-0.5091971	Kurtosis	-0.182648
Uncorrected SS	750	Corrected SS	109.333333
Coeff Variation	45.2533624	Std Error Mean	1.90904281