Creating a date macro

[LMSSAS]

Can anyone provide insight on how to create a marco for the below dates? i want the yearmonth to automatically update in the code. 

proc sql;
create table AEP_Summary as
select *, 
Case
	when a.yearmonth = 202110 THEN 'AEP' 
	when a.yearmonth = 202111 THEN 'AEP' 
	when a.yearmonth = 202112 THEN 'AEP' 
	else " "
	end as 'AEP_Status'n,
Case
	when a.yearmonth = 202101 THEN 'NON_AEP' 
	when a.yearmonth = 202102 THEN 'NON_AEP' 
	when a.yearmonth = 202103 THEN 'NON_AEP'
	when a.yearmonth = 202104 THEN 'NON_AEP' 
	when a.yearmonth = 202105 THEN 'NON_AEP' 
	when a.yearmonth = 202106 THEN 'NON_AEP'
	when a.yearmonth = 202107 THEN 'NON_AEP' 
	when a.yearmonth = 202108 THEN 'NON_AEP' 
	when a.yearmonth = 202109 THEN 'NON_AEP'
	else " "
	end as 'NON_AEP_Status'n
From Data a
;
quit;

[PaigeMiller]

 i want the yearmonth to automatically update in the code. 

Update according to what rules?

[LMSSAS]

bascially to be current with the sysdate. as a new year and a new month I would like the dates to automatically change, is that specific enough? I have never written a macro before

[PaigeMiller]

---

@LMSSAS wrote:
bascially to be current with the sysdate. as a new year and a new month I would like the dates to automatically change, is that specific enough? I have never written a macro before

---

Automatically change to what?

Give concrete examples. What should the code be when the date is Feb 18, 2022? What should the code do when the date is August 6, 2022? What rules should the macro implement?

I always advise people to write code without macros and without macro variables BEFORE you try to create a macro version of the code. Show me the code (without macros and without macro variables) for Feb 18, 2022. Show me the code (without macros and without macro variables) for Aug 6, 2022. 

[LMSSAS]

My date is a year and month only (202202) no days in this date, so When the month is January - September the obs in the data will be "NON_AEP" if the months are Oct - Dec those obs will be "AEP" I have a colum AEP_Status that I want to fill in .

[PaigeMiller]

If those are the rules, and I'm understanding you properly, you don't need a macro at all.

In a data step, if yearmonth is an integer such as 202110

if mod(yearmonth)<=9 then aep_status='NON_AEP';
else aep_status='AEP';

in SQL

case when mod(yearmonth,100)<=9 then 'NON_AEP' else 'AEP' end as aep_status

Why are you talking about macros when your code in your original post is essentially the same as above? Macros create dynamic code that changes according to some conditions, you have not described anything like that.

[LMSSAS · Accepted solution]

Yes, I understand now. i think I started thinking macros because as the months change the status of my obs will change, completely wrong logic. i totally get what you explained, the case statement does exactly what I want. Thanks for taking the time to explain

[ballardw]

If your not-an-actual-date value YearMonth (I can tell this with the comparisons you use) were an actual date you could use logic like:

case month(datevalue) in (10,11,12) then 'AEP'

else ' ' as variable

,case (month(datevalue) in (1:9) then  'NON_AEP'

note the "datevalue" above could be the Today() function to evaluate todays date

You will find working with SAS that it is almost always better to have "date" information as date valued variables as formats and many functions available to work with dates means you don't have to write a lot of not always obvious or simple code to deal with non-dates that you want to use as such. For example: your code would need to be rewritten constantly to deal with the "year" changes because it is not actually built into the numeric value.

Note that poor values can lead to not-obvious code. You can get the number of a month from that 6 digit string using the MOD function:  month=mod(yearmonth,100) which seems to be what you need.

Don't see where macro variables or macro comes into this process.

Creating two variables is actually pretty poor practice in general to handle a two-level value, and character makes it even harder to do many things. If this were my data I would create a 1/0 numeric variable with 1 meaning "true" for 'AEP', assign a custom format if I really needed to see the text 'AEP' or 'NON_AEP'.

1/0 variables are easy to use for many tasks. Example SUM of the variable (for any grouping approach) is the number of "true" and the mean is the decimal percentage of "true". When you do two variables you cause yourself a lot of extra work to ever get a percentage of values.

[LMSSAS]

Thank you for the explanation and information. i am definitely going ot use the 1/0 numeric variable in 1 column to make life easier. Can you tell me what the 100 represents in month=mod(yearmonth,100) ?

[PaigeMiller]

---

@LMSSAS wrote:
Thank you for the explanation and information. i am definitely going ot use the 1/0 numeric variable in 1 column to make life easier. Can you tell me what the 100 represents in month=mod(yearmonth,100) ?

---

You are doing arithmetic modulo 100. Essentially, if you have an integer 202109, it finds the last two digits which is 09

May I give you some advice, @LMSSAS ? Please explain the problem you would like to solve rather than say you are want to create a macro, or you want to create a solution using a specific piece of the SAS language. We wound up having an XY Problem here, which is never a good thing.

[LMSSAS]

Yes, will do!
Thank you!