BookmarkSubscribeRSS Feed
l3xrink
Calcite | Level 5

Hello, I have a dataset that lists dates, and the corresponding YYMMM (for example September 2021 is 21M09). 

 

I would like to take the interval of 21M02 to 23M09 and assign the values 1 through 32 to these in order. 

Using a macro is preferable in case I need to change this interval. Any suggestions? Thanks!!

5 REPLIES 5
Reeza
Super User
Why? How are you storing these dates?
If you have them as SAS dates with a format, they'll sort correctly.
l3xrink
Calcite | Level 5
Hi there are multiple rows that i would like to assign the value of 1,2, etc. depending on the value of YYMMM.
Reeza
Super User
That doesn't actually answer the question. Why the 1/2, etc? I would assume the dates would be more relevant.

Either way, the type of the variable, numeric with date format or character does matter to how a solution is developed. In this situation, a format is recommended, where you create the format via macro with the start date and number of intervals or end date. The format applied will always have the same name saving one step at least.
Tom
Super User Tom
Super User

@l3xrink wrote:
Hi there are multiple rows that i would like to assign the value of 1,2, etc. depending on the value of YYMMM.

If you want to assign 1,2,3 just use a retained variable.

data want;
  set have;
  by id;
  seq+1;
  if first.id then seq=1;
run;

If you want to convert MONTH into relative month number then use INTCK() function.  Add one if you want to number from 1 instead of from 0.

 

 If they are not already dates that just have the YYMM5. format attached (who displays dates without the century number?).

%let basedate = '01jan2011'd;
data want;
  set have;
  month_number = 1 + intck('month',&basedate,yymmm);
run;  

If they are strings then first convert them into a date value.

%let basedate = '01jan2011'd;
data want;
  set have;
  month_number = 1 + intck('month',&basedate,input(cats(compress(yymmm,'Mm'),'01'),yymmdd.));
run;  

 

Astounding
PROC Star
We don't really know whether your variable is character or numeric. If it is character, consider the possibility of doing nothing. The values are already in sorted order. And they would be easy to subset:

if "22M07" <= date <= "23M02";

Do you really want to create the situation where the value "32" changes its meaning depending on the data set that was used?

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 783 views
  • 0 likes
  • 4 in conversation