topic Re: better idea of writing a macro in SAS Programming

better idea of writing a macro

mona4u — Mon, 21 May 2018 20:12:29 GMT

Hi,

I'm trying to write a macro variable that can pull the value of this month and this year and I also want to create another macro variable that pulls the previous month without specifying the year bc of December gonna be in last year for example

I wrote this code but this code will not be good to pull the right values next January for example

bc the previous month gonna be in the previous year

data Asterias2; 
set Asterias1; 
 
format complete_date ddmmyy10.; 
if month(complete_date)=&thissmonth and year(complete_date)=&thissyear  then   count=1;  
if month(complete_date)=%eval(&thissmonth-1) and year(complete_date)=&thissyear then cc=1; 
 
run;  


this is my data

Obs	complete_date	count	cc
1	29/05/2015	.	.
2	29/05/2015	.	.
3	10/6/2015	.	.
4	2/6/2015	.	.
5	10/6/2015	.	.
6	10/6/2015	.	.
7	10/6/2015	.	.
8	10/6/2015	.	.
9	2/7/2015	.	.
10	2/7/2015	.	.
11	6/7/2015	.	.
12	3/7/2015	.	.
13	18/11/2015	.	.
14	18/11/2015	.	.
15	18/11/2015	.	.
16	18/11/2015	.	.
17	24/06/2016	.	.
18	24/06/2016	.	.
19	24/06/2016	.	.
20	24/06/2016	.	.
21	27/06/2017	.	.
22	27/06/2017	.	.
23	30/06/2017	.	.
24	30/06/2017	.	.
25	8/12/2015	.	.
26	8/12/2015	.	.
27	14/02/2016	.	.
28	8/12/2015	.	.
29	12/12/2015	.	.
30	12/12/2015	.	.
31	14/02/2016	.	.
32	12/12/2015	.	.
33	31/03/2016	.	.
34	31/03/2016	.	.
35	31/03/2016	.	.
36	31/03/2016	.	.
37	12/4/2016	.	.
38	12/4/2016	.	.
39	12/4/2016	.	.
40	12/4/2016	.	.
41	12/5/2016	.	.
42	12/5/2016	.	.
43	16/05/2016	.	.
44	14/05/2016	.	.
45	28/09/2016	.	.
46	28/09/2016	.	.
47	29/09/2016	.	.
48	28/09/2016	.	.
49	30/03/2017	.	.
50	30/03/2017	.	.
51	31/03/2017	.	.
52	31/03/2017	.	.
53	14/03/2018	.	.
54	14/03/2018	.	.
55	14/03/2018	.	.
56	14/03/2018	.	.
57	16/09/2016	.	.
58	16/09/2016	.	.
59	16/09/2016	.	.
60	16/09/2016	.	.
61	28/09/2016	.	.
62	28/09/2016	.	.
63	29/09/2016	.	.
64	29/09/2016	.	.
65	20/10/2016	.	.
66	20/10/2016	.	.
67	20/10/2016	.	.
68	20/10/2016	.	.
69	28/03/2017	.	.
70	28/03/2017	.	.
71	29/03/2017	.	.
72	28/03/2017	.	.
73	9/10/2017	.	.
74	9/10/2017	.	.
75	10/10/2017	.	.
76	9/10/2017	.	.
77	4/4/2017	.	.
78	4/4/2017	.	.
79	4/4/2017	.	.
80	4/4/2017	.	.
81	18/04/2017	.	.
82	18/04/2017	.	.
83	18/04/2017	.	.
84	18/04/2017	.	.
85	10/5/2017	.	.
86	10/5/2017	.	.
87	12/5/2017	.	.
88	12/5/2017	.	.
89	23/10/2017	.	.
90	23/10/2017	.	.
91	26/10/2017	.	.
92	25/10/2017	.	.
93	9/4/2018	.	1
94	9/4/2018	.	1
95	10/4/2018	.	1
96	10/4/2018	.	1
97	27/07/2017	.	.
98	27/07/2017	.	.
99	28/07/2017	.	.
100	27/07/2017	.	.
101	9/8/2017	.	.
102	9/8/2017	.	.
103	10/8/2017	.	.
104	9/8/2017	.	.
105	5/9/2017	.	.
106	6/9/2017	.	.
107	6/9/2017	.	.
108	6/9/2017	.	.
109	10/1/2018	.	.
110	10/1/2018	.	.
111	11/1/2018	.	.
112	10/1/2018	.	.
113	31/08/2017	.	.
114	31/08/2017	.	.
115	31/08/2017	.	.
116	31/08/2017	.	.
117	13/09/2017	.	.
118	13/09/2017	.	.
119	14/09/2017	.	.
120	14/09/2017	.	.
121	4/10/2017	.	.
122	5/10/2017	.	.
123	10/10/2017	.	.
124	9/10/2017	.	.
125	21/02/2018	.	.
126	21/02/2018	.	.
127	26/02/2018	.	.
128	23/02/2018	.	.
129	8/9/2017	.	.
130	8/9/2017	.	.
131	12/9/2017	.	.
132	.	.	.
133	4/1/2018	.	.
134	.	.	.
135	28/07/2015	.	.
136	28/07/2015	.	.
137	28/07/2015	.	.
138	28/07/2015	.	.
139	7/8/2015	.	.
140	7/8/2015	.	.
141	8/8/2015	.	.
142	8/8/2015	.	.
143	9/9/2015	.	.
144	10/9/2015	.	.
145	11/9/2015	.	.
146	11/9/2015	.	.
147	17/03/2016	.	.
148	17/03/2016	.	.
149	21/03/2016	.	.
150	19/03/2016	.	.
151	23/08/2016	.	.
152	23/08/2016	.	.
153	25/08/2016	.	.
154	24/08/2016	.	.
155	10/8/2017	.	.
156	10/8/2017	.	.
157	11/8/2017	.	.
158	11/8/2017	.	.
159	17/08/2015	.	.
160	17/08/2015	.	.
161	17/08/2015	.	.
162	17/08/2015	.	.
163	9/9/2015	.	.
164	10/9/2015	.	.
165	11/9/2015	.	.
166	11/9/2015	.	.
167	23/09/2015	.	.
168	23/09/2015	.	.
169	24/09/2015	.	.
170	24/09/2015	.	.
171	17/03/2016	.	.
172	17/03/2016	.	.
173	21/03/2016	.	.
174	19/03/2016	.	.
175	23/08/2016	.	.
176	23/08/2016	.	.
177	25/08/2016	.	.
178	24/08/2016	.	.
179	15/08/2017	.	.
180	15/08/2017	.	.
181	17/08/2017	.	.
182	15/08/2017	.	.
183	16/08/2016	.	.
184	16/08/2016	.	.
185	23/08/2016	.	.
186	17/08/2016	.	.
187	26/08/2016	.	.
188	26/08/2016	.	.
189	26/08/2016	.	.
190	26/08/2016	.	.
191	20/09/2016	.	.
192	20/09/2016	.	.
193	21/09/2016	.	.
194	20/09/2016	.	.
195	1/3/2017	.	.
196	1/3/2017	.	.
197	8/3/2017	.	.
198	8/3/2017	.	.
199	30/08/2017	.	.
200	30/08/2017	.	.
201	31/08/2017	.	.
202	31/08/2017	.	.
203	22/04/2017	.	.
204	22/04/2017	.	.
205	22/04/2017	.	.
206	22/04/2017	.	.
207	5/5/2017	.	.
208	5/5/2017	.	.
209	12/5/2017	.	.
210	12/5/2017	.	.
211	2/6/2017	.	.
212	2/6/2017	.	.
213	2/6/2017	.	.
214	2/6/2017	.	.
215	10/11/2017	.	.
216	10/11/2017	.	.
217	13/11/2017	.	.
218	13/11/2017	.	.
219	24/04/2018	.	1
220	24/04/2018	.	1
221	24/04/2018	.	1
222	24/04/2018	.	1
223	30/06/2017	.	.
224	30/06/2017	.	.
225	1/7/2017	.	.
226	30/06/2017	.	.
227	12/7/2017	.	.
228	12/7/2017	.	.
229	13/07/2017	.	.
230	13/07/2017	.	.
231	2/8/2017	.	.
232	4/8/2017	.	.
233	7/8/2017	.	.
234	4/8/2017	.	.
235	18/01/2018	.	.
236	18/01/2018	.	.
237	19/01/2018	.	.
238	18/01/2018	.	.
239	14/02/2016	.	.
240	14/02/2016	.	.
241	14/02/2016	.	.
242	14/02/2016	.	.
243	3/7/2016	.	.
244	3/7/2016	.	.
245	4/7/2016	.	.
246	3/7/2016	.	.
247	16/07/2016	.	.
248	16/07/2016	.	.
249	4/8/2016	.	.
250	16/07/2016	.	.
251	4/8/2016	.	.
252	4/8/2016	.	.
253	4/8/2016	.	.
254	4/8/2016	.	.
255	5/1/2017	.	.
256	5/1/2017	.	.
257	9/1/2017	.	.
258	6/1/2017	.	.
259	18/07/2017	.	.
260	18/07/2017	.	.
261	18/07/2017	.	.
262	18/07/2017	.	.
263	4/7/2016	.	.
264	4/7/2016	.	.
265	5/7/2016	.	.
266	4/7/2016	.	.
267	31/10/2016	.	.
268	31/10/2016	.	.
269	31/10/2016	.	.
270	31/10/2016	.	.
271	10/11/2016	.	.
272	10/11/2016	.	.
273	11/11/2016	.	.
274	11/11/2016	.	.
275	5/12/2016	.	.
276	5/12/2016	.	.
277	6/12/2016	.	.
278	6/12/2016	.	.
279	15/05/2017	.	.
280	15/05/2017	.	.
281	16/05/2017	.	.
282	16/05/2017	.	.
283	6/11/2017	.	.
284	8/11/2017	.	.
285	10/11/2017	.	.
286	10/11/2017	.	.
287	7/7/2017	.	.
288	7/7/2017	.	.
289	7/7/2017	.	.
290	7/7/2017	.	.
291	17/07/2017	.	.
292	17/07/2017	.	.
293	18/07/2017	.	.
294	18/07/2017	.	.
295	10/8/2017	.	.
296	10/8/2017	.	.
297	11/8/2017	.	.
298	11/8/2017	.	.
299	16/02/2018	.	.
300	16/02/2018	.	.
301	16/02/2018	.	.
302	16/02/2018	.	.
303	1/8/2017	.	.
304	1/8/2017	.	.
305	1/8/2017	.	.
306	1/8/2017	.	.
307	11/8/2017	.	.
308	11/8/2017	.	.
309	11/8/2017	.	.
310	11/8/2017	.	.
311	1/9/2017	.	.
312	5/9/2017	.	.
313	6/9/2017	.	.
314	5/9/2017	.	.
315	9/2/2018	.	.
316	28/02/2018	.	.
317	2/3/2018	.	.
318	1/3/2018	.	.
319	2/8/2017	.	.
320	2/8/2017	.	.
321	2/8/2017	.	.
322	2/8/2017	.	.
323	28/08/2017	.	.
324	28/08/2017	.	.
325	29/08/2017	.	.
326	29/08/2017	.	.
327	13/09/2017	.	.
328	13/09/2017	.	.
329	14/09/2017	.	.
330	14/09/2017	.	.
331	4/10/2017	.	.
332	5/10/2017	.	.
333	10/10/2017	.	.
334	9/10/2017	.	.
335	7/3/2018	.	.
336	7/3/2018	.	.
337	7/3/2018	.	.
338	7/3/2018	.	.
339	8/4/2016	.	.
340	8/4/2016	.	.
341	8/4/2016	.	.
342	8/4/2016	.	.
343	20/04/2016	.	.
344	20/04/2016	.	.
345	21/04/2016	.	.
346	21/04/2016	.	.
347	22/06/2016	.	.
348	22/06/2016	.	.
349	23/06/2016	.	.
350	22/06/2016	.	.
351	19/10/2016	.	.
352	19/10/2016	.	.
353	20/10/2016	.	.
354	20/10/2016	.	.
355	1/5/2017	.	.
356	1/5/2017	.	.
357	3/5/2017	.	.
358	1/5/2017	.	.
359	8/5/2018	1	.
360	8/5/2018	1	.
361	9/5/2018	1	.
362	8/5/2018	1	.
363	25/06/2016	.	.
364	25/06/2016	.	.
365	25/06/2016	.	.
366	25/06/2016	.	.
367	28/10/2016	.	.
368	28/10/2016	.	.
369	29/10/2016	.	.
370	28/10/2016	.	.
371	15/11/2016	.	.
372	15/11/2016	.	.
373	16/11/2016	.	.
374	16/11/2016	.	.
375	6/12/2016	.	.
376	6/12/2016	.	.
377	6/12/2016	.	.
378	6/12/2016	.	.
379	12/5/2017	.	.
380	12/5/2017	.	.
381	15/05/2017	.	.
382	14/05/2017	.	.
383	9/11/2017	.	.
384	9/11/2017	.	.
385	10/11/2017	.	.
386	10/11/2017	.	.
387	31/10/2016	.	.
388	31/10/2016	.	.
389	11/11/2016	.	.
390	31/10/2016	.	.
391	21/11/2016	.	.
392	21/11/2016	.	.
393	23/11/2016	.	.
394	23/11/2016	.	.
395	13/12/2016	.	.
396	13/12/2016	.	.
397	14/12/2016	.	.
398	14/12/2016	.	.
399	23/05/2017	.	.
400	23/05/2017	.	.
401	23/05/2017	.	.
402	23/05/2017	.	.
403	29/11/2017	.	.
404	29/11/2017	.	.
405	4/12/2017	.	.
406	2/12/2017	.	.
407	15/04/2017	.	.
408	15/04/2017	.	.
409	16/04/2017	.	.
410	16/04/2017	.	.
411	23/06/2017	.	.
412	23/06/2017	.	.
413	25/06/2017	.	.
414	25/06/2017	.	.
415	16/08/2017	.	.
416	16/08/2017	.	.
417	16/08/2017	.	.
418	16/08/2017	.	.
419	16/08/2017	.	.
420	16/08/2017	.	.
421	16/08/2017	.	.
422	16/08/2017	.	.
423	17/01/2018	.	.
424	17/01/2018	.	.
425	19/01/2018	.	.
426	18/01/2018	.	.
427	9/12/2017	.	.
428	9/12/2017	.	.
429	9/12/2017	.	.
430	9/12/2017	.	.
431	28/12/2017	.	.
432	28/12/2017	.	.
433	29/12/2017	.	.
434	28/12/2017	.	.
435	24/01/2018	.	.
436	24/01/2018	.	.
437	25/01/2018	.	.
438	25/01/2018	.	.
439	25/01/2018	.	.
440	25/01/2018	.	.
441	29/01/2018	.	.
442	26/01/2018	.	.
443	18/03/2016	.	.
444	18/03/2016	.	.
445	18/03/2016	.	.
446	18/03/2016	.	.
447	31/03/2016	.	.
448	31/03/2016	.	.
449	1/4/2016	.	.
450	31/03/2016	.	.
451	16/06/2016	.	.
452	16/06/2016	.	.
453	17/06/2016	.	.
454	16/06/2016	.	.
455	15/09/2016	.	.
456	15/09/2016	.	.
457	16/09/2016	.	.
458	16/09/2016	.	.
459	1/5/2017	.	.
460	1/5/2017	.	.
461	3/5/2017	.	.
462	1/5/2017	.	.
463	31/05/2016	.	.
464	31/05/2016	.	.
465	31/05/2016	.	.
466	3/6/2016	.	.
467	16/06/2016	.	.
468	16/06/2016	.	.
469	16/06/2016	.	.
470	16/06/2016	.	.
471	25/07/2016	.	.
472	25/07/2016	.	.
473	4/8/2016	.	.
474	26/07/2016	.	.
475	15/06/2017	.	.
476	15/06/2017	.	.
477	15/06/2017	.	.
478	15/06/2017	.	.
479	15/06/2017	.	.
480	15/06/2017	.	.
481	15/06/2017	.	.
482	15/06/2017	.	.
483	17/03/2017	.	.
484	17/03/2017	.	.
485	20/03/2017	.	.
486	18/03/2017	.	.
487	2/5/2017	.	.
488	2/5/2017	.	.
489	5/5/2017	.	.
490	5/5/2017	.	.
491	10/5/2017	.	.
492	10/5/2017	.	.
493	12/5/2017	.	.
494	12/5/2017	.	.
495	9/10/2017	.	.
496	9/10/2017	.	.
497	10/10/2017	.	.
498	9/10/2017	.	.
499	6/5/2017	.	.
500	6/5/2017	.	.
501	8/5/2017	.	.
502	7/5/2017	.	.
503	17/05/2017	.	.
504	17/05/2017	.	.
505	18/05/2017	.	.
506	17/05/2017	.	.
507	6/6/2017	.	.
508	6/6/2017	.	.
509	9/6/2017	.	.
510	8/6/2017	.	.
511	6/2/2018	.	.
512	6/2/2018	.	.
513	6/2/2018	.	.
514	6/2/2018	.	.
515	2/5/2017	.	.
516	2/5/2017	.	.
517	.	.	.
518	2/5/2017	.	.
519	2/5/2017	.	.
520	.	.	.
521	2/5/2017	.	.
522	2/5/2017	.	.
523	.	.	.

Re: better idea of writing a macro

art297 — Mon, 21 May 2018 20:37:12 GMT

Easier if you don't use macro variables. e.g.:

data Asterias2; 
  set Asterias1; 
  format complete_date ddmmyy10.; 
  if month(complete_date)=month(today()) and year(complete_date)=year(today()) then count=1;  
  if month(complete_date)=month(intnx('month',today(),-1,'s')) and year(complete_date)=year(intnx('month',today(),-1,'s')) then cc=1; 
run;

Art, CEO, AnalystFinder.com

Re: better idea of writing a macro

Astounding — Mon, 21 May 2018 20:37:45 GMT

So is COMPLETE_DATE a character string, or is it a SAS date?

You can probably accomplish all of this with no macro language, but the answer still requires knowing what is in COMPLETE_DATE.

Re: better idea of writing a macro

Reeza — Mon, 21 May 2018 21:49:45 GMT

From previous questions, it's a SAS date.

@Astounding wrote:

So is COMPLETE_DATE a character string, or is it a SAS date?

You can probably accomplish all of this with no macro language, but the answer still requires knowing what is in COMPLETE_DATE.

Re: better idea of writing a macro

Astounding — Mon, 21 May 2018 22:44:11 GMT

@Reeza you might be right. On the other hand, some of the months have leading zeros and some don't. So I'm not going to assume or conclude. If the original poster wants to clarify, I'll post an answer to match.

Re: better idea of writing a macro

mona4u — Tue, 22 May 2018 13:56:34 GMT

It's a SAS date I really have no clue about why some months have leading 0 and some not

Re: better idea of writing a macro

mona4u — Tue, 22 May 2018 14:24:26 GMT

I want to create a macro of the months bc I have to create a dataset which changes the column headers to read last 6 months each time I run the program. I mean I need to create a macro to use it again when I finalize my report

That's an example of my desired result

Re: better idea of writing a macro

Astounding — Tue, 22 May 2018 14:31:28 GMT

Given that you are starting with a SAS date, here's a base SAS approach that finds whether a date is the current month or the previous month (or neither):

data Asterias2;
set Asterias1;
if _n_=1 then do;

this_month = intnx('month', today(), 0);

prior_month = intnx('month', this_month, -1);

end;

retain this_month prior_month;

first_day = intnx('month', complete_date, 0);
if first_day = this_month then count=1;
else if first_day = prior_month then cc=1;

drop this_month prior_month first_day;
run;

As used here, INTNX returns the first date within a month.

I think this is what you were looking to do ... set flags that indicate whether an observation falls into the current month, or into the previous month. But if that's only similar to your intent, the same tools can probably get you where you want to go.

Re: better idea of writing a macro

mona4u — Tue, 22 May 2018 14:51:59 GMT

I would like if I can create something like that

%let lastmonth1=%sysevalf(month(intnx('month',today(),-1,'s')));

if month(complete_date)= &lastmonth1 and year .....then....

Re: better idea of writing a macro

art297 — Tue, 22 May 2018 15:07:35 GMT

Doing that isn't a problem .. just more complex than when not using the macro language.

When using the macro language each non-macro function all has to be preceded by a %sys() something (e.g., %sysfunc() ) and, unlike the non-macro calls to those same functions, the quoted parameters have to be unquoted.

Art, CEO, AnalystFinder.com

Re: better idea of writing a macro

mona4u — Tue, 22 May 2018 15:18:01 GMT

this is the error that's I'm getting when I'm trying to use the sysfunc even when I don't use the quotations marks I still getting errors

Re: better idea of writing a macro

Reeza — Tue, 22 May 2018 15:34:40 GMT

Please don't post code or errors as images. If I watned to provide the correct code, rather than modify your post I have to type it all out, which is unlikely.

Your code/approach does not work because you can't use %LET and %PUT in a data step the way you're attempting. If you're going to use the variable in a data step, you may as well use a variable and not a macro variable. I'm not sure why there's any macro code here, there is no need for a macro variable or macro logic.

If you want to create a macro variable in a data step, you can use CALL SYMPUTX() instead.

@mona4u wrote:

this is the error that's I'm getting when I'm trying to use the sysfunc even when I don't use the quotations marks I still getting errors

Re: better idea of writing a macro

mona4u — Tue, 22 May 2018 15:40:34 GMT

Thanks for clarifying this point to me

Re: better idea of writing a macro

mona4u — Tue, 22 May 2018 15:47:41 GMT

your answer was really helpful the macro worked with me this time

Re: better idea of writing a macro

art297 — Tue, 22 May 2018 15:51:06 GMT

I agree with what @Reeza said but, again, no need to put the value in a macro variable.

However, back to your question and comments, aside from the fact that you were trying to use macro code within a datastep, no, you didn't remove all of the quotes.

The following is one way you could write it outside of a datastep:

%let lastmonth=%sysfunc(month(%sysfunc(intnx(month,%sysfunc(today()),-1,s)))); 
%put &lastmonth.;

Art, CEO, AnalystFinder.com