BookmarkSubscribeRSS Feed
Sean_OConnor
Fluorite | Level 6

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,

 

8 REPLIES 8
ballardw
Super User

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.

Kurt_Bremser
Super User

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.

Sean_OConnor
Fluorite | Level 6

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?

Kurt_Bremser
Super User

@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');
Sean_OConnor
Fluorite | Level 6

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');
Reeza
Super User

@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. 

Kurt_Bremser
Super User

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.

PaigeMiller
Diamond | Level 26

Another solution that does not involve the INTCK function:

 

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

 

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 8338 views
  • 0 likes
  • 5 in conversation