turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Subtract one month from a numeric YYYYMM

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-21-2017 07:10 AM

Hi,

I have a numeric format column with YYYYMM from which I would like to subtract one month which is fine unless the month is january. How can I make the code for when YYYYMM = XXXX01 then it should subtract 89 instead of 1 inorder to get the right year and month? In other words, how can I tell SAS to only look at the last to digits (ie MM) to decide if it should subtract by 89 or 1?

Your help is much appreciated!

Accepted Solutions

Solution

07-21-2017
08:35 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Caribu

07-21-2017 07:25 AM

First of all, dates and date-related values should always be kept as SAS date values, and formatted as needed.

For months, you store the first of the month and use a format that displays only year and month.

So you first convert your value, and then use the proper function:

```
data want;
set have;
datevar = input(put(datevar,6.)!!'01',yymmdd8.);
format datevar yymmn6.;
datevar = intnx('month',datevar,-1);
run;
```

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Caribu

07-21-2017 07:14 AM

Use the SAS function intnx, this takes into account years:

want=intnx('month',your_date,-1);

Solution

07-21-2017
08:35 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Caribu

07-21-2017 07:25 AM

First of all, dates and date-related values should always be kept as SAS date values, and formatted as needed.

For months, you store the first of the month and use a format that displays only year and month.

So you first convert your value, and then use the proper function:

```
data want;
set have;
datevar = input(put(datevar,6.)!!'01',yymmdd8.);
format datevar yymmn6.;
datevar = intnx('month',datevar,-1);
run;
```

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

07-21-2017 07:52 AM

Thank you, this seems to work just fine. Only I need to have the date stored as a numeric since the datawarehouse stores dates as integers. So I've gotten the YYYYMM I needed in date format. How do I get it back to an integer with the same numbers (YYYYMM)?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Caribu

07-21-2017 08:31 AM - edited 07-21-2017 08:32 AM

Just do the conversion to a new variable, so you keep the original one. Although there is absolutely no reason for it. Dates are dates, storing them any other way just causes unnecessary work (you experienced that right here!) and is therefore stupid. I don't advise SAS newbies to do stupid things.

And keep in mind that SAS also stores dates as integers, counting the days from 01-01-1960 in both directions. That's why i could convert in place.

This is also the method used by all database systems and spreadsheet calculators I know of. They just use different basedates (31-12-1899 with most spreadsheet sw).

The yymmn6. format already displays the data the way you want it, so you just need to input that into a number:

`silly_date = input(put(real_date,yymmn6.),6.);`

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

07-21-2017 08:38 AM

Thank you very much!