Creating custom fields to filter date

[Renan_Crepaldi]

Hello, everyone!

I'm trying to create custom fields to filter the last 12 months of a data set. The point is that the only column that contains a date variable is a numeric YYYYMM field.

I've succesfully created a first method, but it has a flaw. When it gets to January, my calculation will give a wrong result:

data _null_;
	FORMAT STARTDATE FINALDATE BEST6.;
	STARTDATE = ((YEAR(TODAY())-1)*100)+MONTH(TODAY());
	FINALDATE = (YEAR(TODAY())*100)+MONTH(TODAY())-1;
	CALL SYMPUT ('STARTDATE',STARTDATE);
	CALL SYMPUT ('FINALDATE',FINALDATE);
RUN;

%PUT &STARTDATE.;
%PUT &FINALDATE.;

Is there a better way to make this function work? I know that the INTX function would be more correct, but I don't know how to adapt it for a numeric date.

Thanks!

[PaigeMiller]

---

@Renan_Crepaldi wrote:

Hello, everyone!

I'm trying to create custom fields to filter the last 12 months of a data set. The point is that the only column that contains a date variable is a numeric YYYYMM field.

I've succesfully created a first method, but it has a flaw. When it gets to January, my calculation will give a wrong result:

data _null_;
	FORMAT STARTDATE FINALDATE BEST6.;
	STARTDATE = ((YEAR(TODAY())-1)*100)+MONTH(TODAY());
	FINALDATE = (YEAR(TODAY())*100)+MONTH(TODAY())-1;
	CALL SYMPUT ('STARTDATE',STARTDATE);
	CALL SYMPUT ('FINALDATE',FINALDATE);
RUN;

%PUT &STARTDATE.;
%PUT &FINALDATE.;

Is there a better way to make this function work? I know that the INTX function would be more correct, but I don't know how to adapt it for a numeric date.

Thanks!

---

This creation of date fields such as 201901 are usually a poor choice, for the reason you have found.

Normally, you want to use SAS date values, which are integers that indicate the number of days since January 1, 1960, and so adding one month to December 2018 takes you into January 2019. This is particularly easy using the INTNX function. Since these date numbers are not really useful when humans look at the date numbers, you want to format them to make them human-readable.

data _null_;
    startdate=intnx('year',today(),-1,'s');
    finaldate=intnx('day',today(),-1,'s');
    call symputx('startdate',startdate);
    call symputx('finaldate',finaldate);
run;

%put &startdate %sysfunc(putn(&startdate,yymm8.));
%put &finaldate %sysfunc(putn(&finaldate,yymm8.)); 

So macro variables should contain valid SAS date values, the number of days since Jan 1 1960, and they should only be formatted when humans need to look at them, such as output tables or if a human has to provide input to a macro/program.

[Renan_Crepaldi]

Hi, Paige. Thank you so much for replying!

This method works just fine. The results are exactly what I want, but the query that uses this dates doesn't understand this format.

As I mentioned, the field that I'll use as a filter is numeric, and it's using the date value resulted wrongly.

How can I convert the result to a numeric YYYYMM?

Thank you again!

Best regards.

[PaigeMiller]

Show us a portion of the data you are using, and the query that doesn't work. Also, please confirm and state clearly whether this number that looks like 201812 is truly numeric and not character.

[Renan_Crepaldi]

Of course, Paige. Sorry for not having specified these elements.

The field that I'm using as filter have these properties:

After running the code you sent, the results are:

35         %put &DTINICIAL %sysfunc(putn(&DTINICIAL,yymm8.));
21520  2018M12
36         %put &DTFINAL %sysfunc(putn(&DTFINAL,yymm8.));
21855  2019M11

Using them as filters will results on null values, since there aren't data between these "dates" (21520 and 21855).

If you need more information, please, tell me.

Thanks!

[PaigeMiller]

Show us a portion of the data you are using

[Renan_Crepaldi]

Sorry, Paige. The data that I'm working on is from my company, and I was afraid of sharing it.

I adapted it to English so you can understand (hopefully hahaha):

Thanks for the patience.

Best regards.

[PaigeMiller · Accepted solution]

If you are receiving the values of NUM_AND_MES in that format, then you will have to deal with them in that format. If you are creating them in that format, then I strongly suggest you change the program to have variable NUM_AND_MES as SAS data values (integers showing number of days since Jan 1, 1960) and then format them to be human-understandable.

If they arrive as 201501 then this will allow your SQL to work properly

where &dtinicial<=input(put(num_and_mes,7.),yymmn6.)<=&dtfinal

This will probably be inefficient for very large data sets.

[Renan_Crepaldi]

Thank you very much for helping! The filter worked just fine.

This table was programmed by others some time ago, but I'll totally consider revisiting it to adapt this field to SAS date.

I'm kinda new to SAS language, and I don't really know the best practices.

Best regards!