DATA Step, Macro, Functions and more

better idea of writing a macro

Accepted Solution Solved
Reply
Regular Contributor
Posts: 158
Accepted Solution

better idea of writing a macro

[ Edited ]

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_datecountcc
129/05/2015..
229/05/2015..
310/6/2015..
42/6/2015..
510/6/2015..
610/6/2015..
710/6/2015..
810/6/2015..
92/7/2015..
102/7/2015..
116/7/2015..
123/7/2015..
1318/11/2015..
1418/11/2015..
1518/11/2015..
1618/11/2015..
1724/06/2016..
1824/06/2016..
1924/06/2016..
2024/06/2016..
2127/06/2017..
2227/06/2017..
2330/06/2017..
2430/06/2017..
258/12/2015..
268/12/2015..
2714/02/2016..
288/12/2015..
2912/12/2015..
3012/12/2015..
3114/02/2016..
3212/12/2015..
3331/03/2016..
3431/03/2016..
3531/03/2016..
3631/03/2016..
3712/4/2016..
3812/4/2016..
3912/4/2016..
4012/4/2016..
4112/5/2016..
4212/5/2016..
4316/05/2016..
4414/05/2016..
4528/09/2016..
4628/09/2016..
4729/09/2016..
4828/09/2016..
4930/03/2017..
5030/03/2017..
5131/03/2017..
5231/03/2017..
5314/03/2018..
5414/03/2018..
5514/03/2018..
5614/03/2018..
5716/09/2016..
5816/09/2016..
5916/09/2016..
6016/09/2016..
6128/09/2016..
6228/09/2016..
6329/09/2016..
6429/09/2016..
6520/10/2016..
6620/10/2016..
6720/10/2016..
6820/10/2016..
6928/03/2017..
7028/03/2017..
7129/03/2017..
7228/03/2017..
739/10/2017..
749/10/2017..
7510/10/2017..
769/10/2017..
774/4/2017..
784/4/2017..
794/4/2017..
804/4/2017..
8118/04/2017..
8218/04/2017..
8318/04/2017..
8418/04/2017..
8510/5/2017..
8610/5/2017..
8712/5/2017..
8812/5/2017..
8923/10/2017..
9023/10/2017..
9126/10/2017..
9225/10/2017..
939/4/2018.1
949/4/2018.1
9510/4/2018.1
9610/4/2018.1
9727/07/2017..
9827/07/2017..
9928/07/2017..
10027/07/2017..
1019/8/2017..
1029/8/2017..
10310/8/2017..
1049/8/2017..
1055/9/2017..
1066/9/2017..
1076/9/2017..
1086/9/2017..
10910/1/2018..
11010/1/2018..
11111/1/2018..
11210/1/2018..
11331/08/2017..
11431/08/2017..
11531/08/2017..
11631/08/2017..
11713/09/2017..
11813/09/2017..
11914/09/2017..
12014/09/2017..
1214/10/2017..
1225/10/2017..
12310/10/2017..
1249/10/2017..
12521/02/2018..
12621/02/2018..
12726/02/2018..
12823/02/2018..
1298/9/2017..
1308/9/2017..
13112/9/2017..
132...
1334/1/2018..
134...
13528/07/2015..
13628/07/2015..
13728/07/2015..
13828/07/2015..
1397/8/2015..
1407/8/2015..
1418/8/2015..
1428/8/2015..
1439/9/2015..
14410/9/2015..
14511/9/2015..
14611/9/2015..
14717/03/2016..
14817/03/2016..
14921/03/2016..
15019/03/2016..
15123/08/2016..
15223/08/2016..
15325/08/2016..
15424/08/2016..
15510/8/2017..
15610/8/2017..
15711/8/2017..
15811/8/2017..
15917/08/2015..
16017/08/2015..
16117/08/2015..
16217/08/2015..
1639/9/2015..
16410/9/2015..
16511/9/2015..
16611/9/2015..
16723/09/2015..
16823/09/2015..
16924/09/2015..
17024/09/2015..
17117/03/2016..
17217/03/2016..
17321/03/2016..
17419/03/2016..
17523/08/2016..
17623/08/2016..
17725/08/2016..
17824/08/2016..
17915/08/2017..
18015/08/2017..
18117/08/2017..
18215/08/2017..
18316/08/2016..
18416/08/2016..
18523/08/2016..
18617/08/2016..
18726/08/2016..
18826/08/2016..
18926/08/2016..
19026/08/2016..
19120/09/2016..
19220/09/2016..
19321/09/2016..
19420/09/2016..
1951/3/2017..
1961/3/2017..
1978/3/2017..
1988/3/2017..
19930/08/2017..
20030/08/2017..
20131/08/2017..
20231/08/2017..
20322/04/2017..
20422/04/2017..
20522/04/2017..
20622/04/2017..
2075/5/2017..
2085/5/2017..
20912/5/2017..
21012/5/2017..
2112/6/2017..
2122/6/2017..
2132/6/2017..
2142/6/2017..
21510/11/2017..
21610/11/2017..
21713/11/2017..
21813/11/2017..
21924/04/2018.1
22024/04/2018.1
22124/04/2018.1
22224/04/2018.1
22330/06/2017..
22430/06/2017..
2251/7/2017..
22630/06/2017..
22712/7/2017..
22812/7/2017..
22913/07/2017..
23013/07/2017..
2312/8/2017..
2324/8/2017..
2337/8/2017..
2344/8/2017..
23518/01/2018..
23618/01/2018..
23719/01/2018..
23818/01/2018..
23914/02/2016..
24014/02/2016..
24114/02/2016..
24214/02/2016..
2433/7/2016..
2443/7/2016..
2454/7/2016..
2463/7/2016..
24716/07/2016..
24816/07/2016..
2494/8/2016..
25016/07/2016..
2514/8/2016..
2524/8/2016..
2534/8/2016..
2544/8/2016..
2555/1/2017..
2565/1/2017..
2579/1/2017..
2586/1/2017..
25918/07/2017..
26018/07/2017..
26118/07/2017..
26218/07/2017..
2634/7/2016..
2644/7/2016..
2655/7/2016..
2664/7/2016..
26731/10/2016..
26831/10/2016..
26931/10/2016..
27031/10/2016..
27110/11/2016..
27210/11/2016..
27311/11/2016..
27411/11/2016..
2755/12/2016..
2765/12/2016..
2776/12/2016..
2786/12/2016..
27915/05/2017..
28015/05/2017..
28116/05/2017..
28216/05/2017..
2836/11/2017..
2848/11/2017..
28510/11/2017..
28610/11/2017..
2877/7/2017..
2887/7/2017..
2897/7/2017..
2907/7/2017..
29117/07/2017..
29217/07/2017..
29318/07/2017..
29418/07/2017..
29510/8/2017..
29610/8/2017..
29711/8/2017..
29811/8/2017..
29916/02/2018..
30016/02/2018..
30116/02/2018..
30216/02/2018..
3031/8/2017..
3041/8/2017..
3051/8/2017..
3061/8/2017..
30711/8/2017..
30811/8/2017..
30911/8/2017..
31011/8/2017..
3111/9/2017..
3125/9/2017..
3136/9/2017..
3145/9/2017..
3159/2/2018..
31628/02/2018..
3172/3/2018..
3181/3/2018..
3192/8/2017..
3202/8/2017..
3212/8/2017..
3222/8/2017..
32328/08/2017..
32428/08/2017..
32529/08/2017..
32629/08/2017..
32713/09/2017..
32813/09/2017..
32914/09/2017..
33014/09/2017..
3314/10/2017..
3325/10/2017..
33310/10/2017..
3349/10/2017..
3357/3/2018..
3367/3/2018..
3377/3/2018..
3387/3/2018..
3398/4/2016..
3408/4/2016..
3418/4/2016..
3428/4/2016..
34320/04/2016..
34420/04/2016..
34521/04/2016..
34621/04/2016..
34722/06/2016..
34822/06/2016..
34923/06/2016..
35022/06/2016..
35119/10/2016..
35219/10/2016..
35320/10/2016..
35420/10/2016..
3551/5/2017..
3561/5/2017..
3573/5/2017..
3581/5/2017..
3598/5/20181.
3608/5/20181.
3619/5/20181.
3628/5/20181.
36325/06/2016..
36425/06/2016..
36525/06/2016..
36625/06/2016..
36728/10/2016..
36828/10/2016..
36929/10/2016..
37028/10/2016..
37115/11/2016..
37215/11/2016..
37316/11/2016..
37416/11/2016..
3756/12/2016..
3766/12/2016..
3776/12/2016..
3786/12/2016..
37912/5/2017..
38012/5/2017..
38115/05/2017..
38214/05/2017..
3839/11/2017..
3849/11/2017..
38510/11/2017..
38610/11/2017..
38731/10/2016..
38831/10/2016..
38911/11/2016..
39031/10/2016..
39121/11/2016..
39221/11/2016..
39323/11/2016..
39423/11/2016..
39513/12/2016..
39613/12/2016..
39714/12/2016..
39814/12/2016..
39923/05/2017..
40023/05/2017..
40123/05/2017..
40223/05/2017..
40329/11/2017..
40429/11/2017..
4054/12/2017..
4062/12/2017..
40715/04/2017..
40815/04/2017..
40916/04/2017..
41016/04/2017..
41123/06/2017..
41223/06/2017..
41325/06/2017..
41425/06/2017..
41516/08/2017..
41616/08/2017..
41716/08/2017..
41816/08/2017..
41916/08/2017..
42016/08/2017..
42116/08/2017..
42216/08/2017..
42317/01/2018..
42417/01/2018..
42519/01/2018..
42618/01/2018..
4279/12/2017..
4289/12/2017..
4299/12/2017..
4309/12/2017..
43128/12/2017..
43228/12/2017..
43329/12/2017..
43428/12/2017..
43524/01/2018..
43624/01/2018..
43725/01/2018..
43825/01/2018..
43925/01/2018..
44025/01/2018..
44129/01/2018..
44226/01/2018..
44318/03/2016..
44418/03/2016..
44518/03/2016..
44618/03/2016..
44731/03/2016..
44831/03/2016..
4491/4/2016..
45031/03/2016..
45116/06/2016..
45216/06/2016..
45317/06/2016..
45416/06/2016..
45515/09/2016..
45615/09/2016..
45716/09/2016..
45816/09/2016..
4591/5/2017..
4601/5/2017..
4613/5/2017..
4621/5/2017..
46331/05/2016..
46431/05/2016..
46531/05/2016..
4663/6/2016..
46716/06/2016..
46816/06/2016..
46916/06/2016..
47016/06/2016..
47125/07/2016..
47225/07/2016..
4734/8/2016..
47426/07/2016..
47515/06/2017..
47615/06/2017..
47715/06/2017..
47815/06/2017..
47915/06/2017..
48015/06/2017..
48115/06/2017..
48215/06/2017..
48317/03/2017..
48417/03/2017..
48520/03/2017..
48618/03/2017..
4872/5/2017..
4882/5/2017..
4895/5/2017..
4905/5/2017..
49110/5/2017..
49210/5/2017..
49312/5/2017..
49412/5/2017..
4959/10/2017..
4969/10/2017..
49710/10/2017..
4989/10/2017..
4996/5/2017..
5006/5/2017..
5018/5/2017..
5027/5/2017..
50317/05/2017..
50417/05/2017..
50518/05/2017..
50617/05/2017..
5076/6/2017..
5086/6/2017..
5099/6/2017..
5108/6/2017..
5116/2/2018..
5126/2/2018..
5136/2/2018..
5146/2/2018..
5152/5/2017..
5162/5/2017..
517...
5182/5/2017..
5192/5/2017..
520...
5212/5/2017..
5222/5/2017..
523...

Accepted Solutions
Solution
‎05-22-2018 11:54 AM
PROC Star
Posts: 8,163

Re: better idea of writing a macro

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

 

View solution in original post


All Replies
Solution
‎05-22-2018 11:54 AM
PROC Star
Posts: 8,163

Re: better idea of writing a macro

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

 

Regular Contributor
Posts: 158

Re: better idea of writing a macro

[ Edited ]

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 

 

 

 

 

 

 

 

Capture11.PNG

Regular Contributor
Posts: 158

Re: better idea of writing a macro

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.... 

PROC Star
Posts: 8,163

Re: better idea of writing a macro

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

 

Regular Contributor
Posts: 158

Re: better idea of writing a macro

Capture12.PNGthis 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  

Super User
Posts: 23,662

Re: better idea of writing a macro

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:

Capture12.PNGthis 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  



 

Regular Contributor
Posts: 158

Re: better idea of writing a macro

Thanks for clarifying this point to me  

Regular Contributor
Posts: 158

Re: better idea of writing a macro

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

PROC Star
Posts: 8,163

Re: better idea of writing a macro

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

 

Super User
Posts: 6,751

Re: better idea of writing a macro

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.

Super User
Posts: 23,662

Re: better idea of writing a macro

Posted in reply to Astounding

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.


 

Super User
Posts: 6,751

Re: better idea of writing a macro

@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.

Regular Contributor
Posts: 158

Re: better idea of writing a macro

Posted in reply to Astounding

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

Super User
Posts: 6,751

Re: better idea of writing a macro

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 215 views
  • 5 likes
  • 4 in conversation