Desktop productivity for business analysts and programmers

Change all date variables to first day of the month

Reply
Contributor
Posts: 44

Change all date variables to first day of the month

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,

 

Super User
Posts: 11,128

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

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.

Super User
Posts: 7,431

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

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

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?

Super User
Posts: 7,431

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


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');
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 44

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

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');
Super User
Posts: 19,134

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


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. 

Super User
Posts: 7,431

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,789

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

Another solution that does not involve the INTCK function:

 

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

 

Ask a Question
Discussion stats
  • 8 replies
  • 221 views
  • 0 likes
  • 5 in conversation