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

Hi All,

 

I do reports that use the previous Sunday as the end date. I can calculate the Sunday based on the data _null_ step, but how do I now use that in the Where Week_end_dt =  'Sunday.'d;

 

 

data _null_; 
    ref=today();
    sunday = intnx('week1.2',ref,-1,'End');
    format _all_ Date10.; 
    put Sunday;
    run; 



	
Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  'Sunday.'d;
	RUN;
		

 

Cheers

 

Dean

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Macro variables, in this usage are text replacement, literally like find and replace.

INTNX is used to align the date (4th parameter) to the beginning of the week. 

 

data _null_;
call symputx('DateVar', put(intnx('week', today(), 0, 'b'), date9.));
run;

Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  "&DateVar"d;
	RUN;

Or you could just apply that in your WHERE clause directly.

 

Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  intnx('week', today(), 0, 'b');
	RUN;

View solution in original post

8 REPLIES 8
TomKari
Onyx | Level 15

Good start! This is where you need SAS macro variables. They are always character, so you need to convert your date into a character version.

 

In your data _null_ step, switch out your format and put statements, and instead use

 

call symput('DateVar', put(sunday, date9.));

 

to load the formatted date into a macro variable named DateVar.

And then in your second step, use

 

Where Week_end_dt =  "&Sunday."d;

 

You need double quotes to allow the substitution.

Tom

DME790
Pyrite | Level 9

Thanks Tom,

 

I must still be missing something sorry. I have changed the code to:

 

 

data _null_; 
       call symput('DateVar', put(sunday, date9.));
    run; 

%PUT &Sunday.;

Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  "&Sunday."d;
	RUN;

and I get this error

 

29          Where Week_end_dt =  "&Sunday."d;
NOTE: Line generated by the macro variable "SUNDAY".
29           "" 10SEP2017"
                __
                22
                76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE,
              LT, NE, NOT, OR, ^, ^=, |, ||, ~, ~=. 

ERROR 76-322: Syntax error, statement will be ignored.

 

 

Thanks for your help.

 

Dean

 

TomKari
Onyx | Level 15

Sorry, brain fart.

 

The name of the macro variabel is DateVar. I forgot to change it in the second reference.

 

Use &DateVar. instead of &Sunday.

 

Tom

DME790
Pyrite | Level 9

All good Tom - appreciate the help.

 

 

I'm now getting no data returned due to the date being blank (if I read the note properly.

 

NOTE: There were 0 observations read from the data set SDPTEL.EMPLOYEE_TELEPHONY.
      WHERE Week_end_dt='        .'D;

 

 

 

data _null_;
	call symput('DateVar', put(sunday, date9.));
run;

%PUT &DateVar;


Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  "&DateVar."d;
RUN;

 

 

 

Reeza
Super User

There's no SET statement in your data _null_ step.

DME790
Pyrite | Level 9
Hi Reeza - I'm not sure what you mean by not having a SET statement in the Data _NULL_ step.
Reeza
Super User
data _null_; 
call symput('DateVar', put(sunday, date9.));
run;

 

You can't create a macro variable out of nothing. You're referencing the variable SUNDAY in the data step but there's no data set with a variable named SUNDAY and 'sunday' doesn't have any meaning so I'm not sure what you're trying to do here.

 

You had it working before, but you changed it for some reason?

Reeza
Super User

Macro variables, in this usage are text replacement, literally like find and replace.

INTNX is used to align the date (4th parameter) to the beginning of the week. 

 

data _null_;
call symputx('DateVar', put(intnx('week', today(), 0, 'b'), date9.));
run;

Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  "&DateVar"d;
	RUN;

Or you could just apply that in your WHERE clause directly.

 

Data work.TT_Weekly_report;
	Set Data.Employee;
	Keep Week_end_dt EVENT_DT Cluster Queue_CD TOTAL_CALLS TALK_TIME MBL;
	Where Week_end_dt =  intnx('week', today(), 0, 'b');
	RUN;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 1061 views
  • 0 likes
  • 3 in conversation