topic Re: Change all date variables to first day of the month in SAS Enterprise Guide

Change all date variables to first day of the month

Sean_OConnor — Thu, 20 Apr 2017 14:22:43 GMT

Folks,

I'm currently writing a loading program to create a number of datasets. In total I'm creating 14 datasets and would thing I would like to do is convert any date variables I have to the first day of any specifc month.

For example, for a variable called newdate with one value being 12/4/1978, I would like to change the date to the 1/4/1978.

Is there any general way to include in my program a piece of code which converts all dates to the first of specifc month? I understand how to to this in each general dataset but would like something more effieicient if possible?

Any information would be most welcome.

Kind regards,

Re: Change all date variables to first day of the month

ballardw — Thu, 20 Apr 2017 14:26:18 GMT

Assuming your date varaibles are actually SAS date valued variables.

data want;

set have;

date = intnx('month',date,0,'b');

run;

increments the month by 0 (same month) and uses the begining with the 'b' for alignment.

Re: Change all date variables to first day of the month

Kurt_Bremser — Thu, 20 Apr 2017 14:28:48 GMT

To change newdate to the first of the month, use

newdate = intnx('month',newdate,0,'begin');

To automatically find all date variables in a dataset, fetch all records from dictionary.columns that are numeric and have a date format assigned.

If you have been strict in using date formats, this might be quite easy.

Then you could use data _null_ and call execute to create a data step dynamically to change the values.

Re: Change all date variables to first day of the month

Sean_OConnor — Thu, 20 Apr 2017 14:48:22 GMT

Folks,

A slight deviation to what I've asked. Is it possible to amend the code so that if the date is closer to the end of the month the 31st or 30th the date will be converted to, and if it's closer to the start of the month the the 1st is used?

Re: Change all date variables to first day of the month

Kurt_Bremser — Thu, 20 Apr 2017 14:59:35 GMT

@Sean_OConnor wrote:

Folks,

A slight deviation to what I've asked. Is it possible to amend the code so that if the date is closer to the end of the month the 31st or 30th the date will be converted to, and if it's closer to the start of the month the the 1st is used?

This is also possible through the use of the intnx() function:

if newdate <= intnx('month',newdate,0,'middle')
then newdate = intnx('month',newdate,0,'begin');
else newdate = intnx('month',newdate,0,'end');

Re: Change all date variables to first day of the month

Sean_OConnor — Thu, 20 Apr 2017 15:28:22 GMT

Thanks for this Kurt.

Just a quick question. As I'm new enough to SAS is it possible to extend this piece of code to referenece more than one variable?

if newdate <= intnx('month',newdate,0,'middle')
then newdate = intnx('month',newdate,0,'begin');
else newdate = intnx('month',newdate,0,'end');

The below doesn't work but is what I'm trying to do;

if cease or accountsfrom or accountsto <= intnx('month',cease or accountsfrom or accountsto,0,'middle')
then cease or accountsfrom or accountsto = intnx('month',cease or accountsfrom or accountsto,0,'begin');
else cease or accountsfrom or accountsto = intnx('month',cease or accountsfrom or accountsto,0,'end');

Re: Change all date variables to first day of the month

PaigeMiller — Thu, 20 Apr 2017 15:31:56 GMT

Another solution that does not involve the INTCK function:

newdate = mdy (month(date),1,year(date));

Re: Change all date variables to first day of the month

Reeza — Thu, 20 Apr 2017 20:47:04 GMT

@Sean_OConnor wrote:

Thanks for this Kurt.

Just a quick question. As I'm new enough to SAS is it possible to extend this piece of code to referenece more than one variable?
if newdate <= intnx('month',newdate,0,'middle')
then newdate = intnx('month',newdate,0,'begin');
else newdate = intnx('month',newdate,0,'end');
The below doesn't work but is what I'm trying to do;
if cease or accountsfrom or accountsto <= intnx('month',cease or accountsfrom or accountsto,0,'middle')
then cease or accountsfrom or accountsto = intnx('month',cease or accountsfrom or accountsto,0,'begin');
else cease or accountsfrom or accountsto = intnx('month',cease or accountsfrom or accountsto,0,'end');

No, you can't reference variables in this manner. You can create an array to loop through multiple variables and do the conversion though.

Re: Change all date variables to first day of the month

Kurt_Bremser — Fri, 21 Apr 2017 06:09:27 GMT

If you know the variables in question, you can define an array over them and loop through for resetting the values:

data dataset;
set dataset;
array datevars {*} startdate birthdate enddate datevar1-datevar3;
do i = 1 to dim(datevars);
  if datevars{i} <= intnx('month',datevars{i},0,'middle')
  then datevars{i} = intnx('month',datevars{i},0,'begin');
  else datevars{i} = intnx('month',datevars{i},0,'end');
end;
drop i;
run;

If you don't know the names of the variables, you can extract their names from dictionary.columns:

proc sql noprint;
select name into :varnames seperated by ' '
from dictionary.columns
where
  upcase(memname) = 'DATASET' and upcase(libname) = 'LIB'
  and type = 'num' and format contains 'DDMMYY'
;
quit;

Now you can use &varnames in the array definition. Adapt the where condition to your needs with regard to the format names.