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,
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.
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.
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?
@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');
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');
@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.
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.
Another solution that does not involve the INTCK function:
newdate = mdy (month(date),1,year(date));
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.