BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tparvaiz
Obsidian | Level 7

Hi,

 

I am working on a report that takes in some date variables. these variables are set based on the day of the month that I run it

 

as an example, if I run a report on any day of this week week then the data should be pulled for last week and so on. to keep it simple, here is the matrix I am trying to create

 

 variable names: week date date1 date_today date2
report execution dates Week month Start_date Mid_date End_date
May 8 to May 14 1 may2017 '01may2017'd; '08MAY2017'd '01jun2017'd
May 15 to May 21 2 may2017 '01may2017'd; '15MAY2017'd '01jun2017'd
May 22 to May Last Day 3 may2017 '01may2017'd; '22MAY2017'd '01jun2017'd
June 1  to June 7 4 may2017 '01may2017'd; '01JUN2017'd '01jun2017'd
           
jun 8 to jun 14 1 jun2017 '01jun2017'd; '08jun2017'd '01jul2017'd
jun 15 to jun 21 2 jun2017 '01jun2017'd; '15jun2017'd '01jul2017'd
jun 22 to jun Last Day 3 jun2017 '01jun2017'd; '22jun2017'd '01jul2017'd
jul 1  to jul 7 4 jun2017 '01jun2017'd; '01jul2017'd '01jul2017'd

 

 

based on the above matrix, if I run the macro today (may 23rd) then the dates should be set as follows

 

Week = 3

date = may2017

date1 = '01may2017'd;

date_today = 22MAY2017'd

date2 = '01jun2017'd

 

here is the code that I've created so far

 

 

data _null_;
	if 
		8 <= day(today()) <= 14 then
		do;
			week = 1;
			date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));
			date1 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
			date2 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),1)),date9.))%str(%')d;
			date_today = %str(%')%str(%08)%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.))%str(%')d;
		end;
	else if 15 <= day(today()) <= 21 then
		do;
			week = 2;
			date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));
			date1 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
			date2 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),1)),date9.))%str(%')d;
			date_today = %str(%')15%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.))%str(%')d;
		end;
	else if 22 <= day(today()) < 1 then
		do;
			week = 3;
			date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));
			date1 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
			date2 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),1)),date9.))%str(%')d;
			date_today = %str(%')22%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.))%str(%')d;
		end;
	else if 1 <= day(today()) <= 7 then
		do;
			week = 4;
			date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), -1)),MONYY7.));
			date1 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d;
			date2 = %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
			date_today = %str(%')01%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.))%str(%')d;
		end;
run;

 

data temp_weekly_data;
set db.table(where = ("&date_today"d <= prod_date < "&date2"d));

run;

 

 

-------------------------------- 

 

issue 1) I am getting and error message

 

Expecting an arithmetic expression.

 

quesiton ) can I just call variable, let's say date2, as shown above or do I need to do something else.

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Since you already have DATE2 holding the first day of the current month, DATE_TODAY can be built from that.  There may be more elegant ways, but this should work:

 

day_of_month = day(today());

if day_of_month < 8 then date_today = date2;

else if day_of_month < 15 then date_today = date2 + 7;

else if day_of_month < 22 then date_today = date2 + 14;

else date_today = date2 + 21;

 

 

View solution in original post

13 REPLIES 13
ballardw
Super User

And why don't you have the LOG results with the Error posted?

And in a code box using the forum {i} menu icon?

And why are using %sysfunc in a data step? %sysfunc is to execute a datastep function in macro code.

 

This

date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));

should probably be

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

with possibly a 'b' if you want the first day of the month or 'E' if you want the last day of the month.

And similaryly through the code.

tparvaiz
Obsidian | Level 7

here is the log

 

3313 data _null_;
3314 Day_of_month = day(today());
3315 call symputx('Day_of_month',Day_of_month);
3316
3317 run;

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


3318
3319 data _null_;
3320 if
3321 8 <= day(today()) <= 14 then
3322 do;
3323 week = 1 ;
3324 date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));
3325 date1 =
3325! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3326 date2 =
3326! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),1)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3327 date_today =
3327! %str(%')%str(%08)%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),
3327! 0)),MONYY7.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3328 end;
3329 else if
3330 15 <= day(today()) <= 21 then
3331 do;
3332 week = 2 ;
3333 date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));
3334 date1 =
3334! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3335 date2 =
3335! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),1)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3336 date_today = %str(%')15%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),
3336! 0)),MONYY7.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3337 end;
3338 else if
3339 22 <= day(today()) < 1 then
3340 do;
3341 week = 3 ;
3342 date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), 0)),MONYY7.));
3343 date1 =
3343! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3344 date2 =
3344! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),1)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3345 date_today = %str(%')22%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),
3345! 0)),MONYY7.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3346 end;
3347 else if
3348 1 <= day(today()) <= 7 then
3349 do;
3350 week = 4 ;
3351 date = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()), -1)),MONYY7.));
3352 date1 =
3352! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3353 date2 =
3353! %str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),0)),date9.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3354 date_today = %str(%')01%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),
3354! 0)),MONYY7.))%str(%')d;
386: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 386-185: Expecting an arithmetic expression.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
76: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN
where the error has occurred.
ERROR 76-322: Syntax error, statement will be ignored.
3355 end;
3356 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds

 

Reeza
Super User

Why do you need dates with the quotes/d literal?

Why not use SAS dates throughout, it would be easier to see. To show in that format it means character variables which means you have to process it further. You're making this harder than it has to be basically. 

 

tparvaiz
Obsidian | Level 7

I am trying to get to this matrix

 

  week date date1 date_today date2
report execution dates Report Week Report month Report Start_date Report Mid_date Report End_date
May 8 to May 14 1 may2017 '01may2017'd; '08MAY2017'd '01jun2017'd;
May 15 to May 21 2 may2017 '01may2017'd; '15MAY2017'd '01jun2017'd;
May 22 to May Last Day 3 may2017 '01may2017'd; '22MAY2017'd '01jun2017'd;
June 1  to June 7 4 may2017 '01may2017'd; '01JUN2017'd '01jun2017'd;
           
jun 8 to jun 14 1 jun2017 '01jun2017'd; '08jun2017'd '01jul2017'd;
jun 15 to jun 21 2 jun2017 '01jun2017'd; '15jun2017'd '01jul2017'd;
jun 22 to jun Last Day 3 jun2017 '01jun2017'd; '22jun2017'd '01jul2017'd;
jul 1  to jul 7 4 jun2017 '01jun2017'd; '01jul2017'd

'01jul2017'd;

 

 

 

her is what I have (as a simplified sample)...

 


data _null_;
format week comma10.0;
format date date1 date2 date_today date9.;
week = 1 ;
date = %sysfunc(intnx('month',today,0));
date1 = %sysfunc(intnx('month',today,0, 'b'));
date2 = %sysfunc(intnx('month',today,1, 'b'));
date_today = %str(%08)%sysfunc(intnx('month',today,0));

call symputx('week',week);
call symputx('date',date);
call symputx('date1',date1);
call symputx('date2',date2);
call symputx('date_today',date_today);
run;

 

 

----------------------

 

150 data _null_;
151 format week comma10.0;
152 format date date1 date2 date_today date9.;
153 week = 1 ;
154 date = %sysfunc(intnx('month',today,0));
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
155 date1 = %sysfunc(intnx('month',today,0, 'b'));
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
156 date2 = %sysfunc(intnx('month',today,1, 'b'));
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
157 date_today = %str(%08)%sysfunc(intnx('month',today,0));
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
NOTE: Line generated by the macro function "STR".
1 %08
-
386
ERROR 386-185: Expecting an arithmetic expression.

NOTE: Line generated by the macro function "STR".
1 %08
-
200
ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: Line generated by the macro function "STR".
1 %08
-
76
ERROR 76-322: Syntax error, statement will be ignored.

158
159
160 call symputx('week',week);
161 call symputx('date',date);
162 call symputx('date1',date1);
163 call symputx('date2',date2);
164 call symputx('date_today',date_today);
165 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds


166
167
168
WARNING: Apparent symbolic reference DATE_TODAY not resolved.
169 %put &week &date &date1 &date2 &date_today;
1 . . . &date_today

 

 

-----------------------------------

 

please advise what am I doing wrong here

 

%put &week &date &date1 &date2 &date_today;

 

 

 

Reeza
Super User

Your mistakes:

 

You're mixing macro and data step logic. For example, you don't need %SYSFUNC() in data step. 

 

TODAY is a function, you're missing the parentheses. 

 

When you're trying to calculate the date_today you're using STR(08) - It's not clear what you're trying to do here. If you're trying to align the date to the end of the period why not use INTNX again with a WEEK interval and put it to the end or beginning?

 

If you're writing a macro, ALWAYS have BASE code that works first. Otherwise you'll run into these issues. 

 

 

tparvaiz
Obsidian | Level 7

here is the modified base code

 


data _null_;
format week comma10.0;
format date date1 date2 date_today date9.;
week = 1 ;
date = intnx('month',today(),0);
date1 = intnx('month',today(),0, 'b');
date2 = intnx('month',today(),1, 'b');
date_today = intnx('week',today(),1);

call symputx('week',week);
call symputx('date',date);
call symputx('date1',date1);
call symputx('date2',date2);
call symputx('date_today',date_today);
run;

 

%put &week &date &date1 &date2 &date_today;

 

output

 

316 %put &week &date &date1 &date2 &date_today;
1 20940 20940 20971 20967

 

-----------------

seems like issue with the format... not sure what causing it as I am using date9. format as you can see from the code above

Reeza
Super User

Well, that's a bit closer 🙂

 

value

specifies a character or numeric constant, variable, or expression. If value is numeric, SAS converts the value to a character string using the BEST. format and does not issue a note to the SAS log. Leading and trailing blanks are removed, and the resulting character string is assigned to the macro variable.

 

If you want to control the format, use PUT() to convert it with DATE9. 

I REALLY REALLY don't recommend this though, use the values you have. Reveiw how SAS calculates dates and why you should use those numbers, I don't have time to write out all the reasons why, but if you understand the basics of dates it'll make sense to you. 

 

 

Astounding
PROC Star

You've gotten most of the way there.  What you need to appreciate is how SAS stores date values.  They are integers, the number of days from January 1, 1960 to that particular day.  So the integers you are getting are perfectly valid SAS dates.  They can even be used in your program below.  This would be perfectly legal:

 

data temp_weekly_data;
set db.table(where = (20940 <= prod_date < 20971));

run;

 

You can get to that simply, since you already have macro variables with the proper values:

 

set db.table (where=(&date1 <= prod_date < &date2));

 

I'm not sure I'm using the proper macro variables to select the date ranges you want, but the lesson here is how to work with dates.  They are integer values.  Always.

Reeza
Super User

@tparvaiz wrote:

Hi,

 

I am working on a report that takes in some date variables. these variables are set based on the day of the month that I run it

 

as an example, if I run a report on any day of this week week then the data should be pulled for last week and so on. to keep it simple, here is the matrix I am trying to create

 

 



Your 'matrix' may be helpful in understanding but it's not a good way to drive your reports. You can use the macro variables directly you don't need to create that matrix. Creating them on the fly each time will have very little overhead in your process. 

 

You never 'finished' this question, did the options here not work in some manner? Otherwise you should consider marking the appropriate answer as the solution. 

 

https://communities.sas.com/t5/Base-SAS-Programming/how-to-automatically-find-week-number-of-the-mon...

tparvaiz
Obsidian | Level 7

Folks, thanks again for your assistance. I think I am getting there.

 

here is my code so far

 

------------------CODE------------------------------

 

 

data _null_;
format week comma10.0;
format date1 date2 date_today date9.;
format date MONYY.;

if
8 <= day(today()) <= 14 then
do;
week = 1 ;
date = intnx('month',today(),0);
date1 = intnx('month',today(),0, 'b');
date2 = intnx('month',today(),1, 'b');
date_today = intnx('week',today(),0);
end;
else if
15 <= day(today()) <= 21 then
do;
week = 2 ;
date = intnx('month',today(),0);
date1 = intnx('month',today(),0, 'b');
date2 = intnx('month',today(),1, 'b');
date_today = intnx('week',today(),0);
end;
else if
22 <= day(today()) <= 31 then
do;
week = 3 ;
date = intnx('month',today(),0);
date1 = intnx('month',today(),0, 'b');
date2 = intnx('month',today(),1, 'b');
date_today = intnx('week',today(),0);
end;
else if
1 <= day(today()) <= 7 then
do;
week = 4 ;
date = intnx('month',today(),-1);
date1 = intnx('month',today(),-1, 'b');
date2 = intnx('month',today(),0, 'b');
date_today = intnx('week',today(),0);
end;

put date1 date2 date_today DATE9.;
put date MONYY.;


call symputx('week',week);
call symputx('date',date);
call symputx('date1',date1);
call symputx('date2',date2);
call symputx('date_today',date_today);

run;

 


%put date = &date. date1 = &date1. date2 = &date2. date_today = &date_today. week = &week.;

 

 

 

--------------------------Log file -----------------------------------------------

 

 

5676 else if
5677 1 <= day(today()) <= 7 then
5678 do;
5679 week = 4 ;
5680 date = intnx('month',today(),-1);
5681 date1 = intnx('month',today(),-1, 'b');
5682 date2 = intnx('month',today(),0, 'b');
5683 date_today = intnx('week',today(),0);
5684 end;
5685
5686 put date1 date2 date_today DATE9.;
5687 put date MONYY.;
5688
5689
5690 call symputx('week',week);
5691 call symputx('date',date);
5692 call symputx('date1',date1);
5693 call symputx('date2',date2);
5694 call symputx('date_today',date_today);
5695
5696 run;

01MAY2017 01JUN2017 21MAY2017
MAY17
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


SYMBOLGEN: Macro variable DATE resolves to 20940
5697
SYMBOLGEN: Macro variable DATE1 resolves to 20940
5698
SYMBOLGEN: Macro variable DATE2 resolves to 20971
5699
SYMBOLGEN: Macro variable DATE_TODAY resolves to 20960
5700
SYMBOLGEN: Macro variable WEEK resolves to 3
5701 %put date = &date. date1 = &date1. date2 = &date2. date_today = &date_today. week = &week.;
date = 20940 date1 = 20940 date2 = 20971 date_today = 20960 week = 3

 

 

 

----------------------------------------------------Remaining Issue ---------------------------------------

my date_today is not set up properly

 

I want date today to be as follows

if today is between 8th day of the month to 14th day of the month: 8th day of the month, as en example 8May2017

if today is between 15th day of the month to 21nd day of the month: 15th day of the month, as en example 15May2017

if today is between 22nd day of the month to 31st day of the month: 22nd day of the month, as en example 22May2017

 

...

 

I tried differnet logic as posted above but nothing is work so far, please advise

 

Thanks again

 

 

 

 

Astounding
PROC Star

Since you already have DATE2 holding the first day of the current month, DATE_TODAY can be built from that.  There may be more elegant ways, but this should work:

 

day_of_month = day(today());

if day_of_month < 8 then date_today = date2;

else if day_of_month < 15 then date_today = date2 + 7;

else if day_of_month < 22 then date_today = date2 + 14;

else date_today = date2 + 21;

 

 

tparvaiz
Obsidian | Level 7

thanks everyone for your assistance

 

Talha

Reeza
Super User

You should use a DO loop rather than repeat your code. I think the code from @Astounding will help with the other date calcs so you don't need the IF statements. 

 

do week=1 to 4;
    date = intnx('month',today(),0);
    date1 = intnx('month',today(),0, 'b');
    date2 = intnx('month',today(),1, 'b');
    date_today = intnx('week',today(),0); 
end;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 5727 views
  • 1 like
  • 4 in conversation