BookmarkSubscribeRSS Feed
DME790
Pyrite | Level 9
Data work.save_Prompt;

Prompt = "Weekend";
Value = "&Excel_Date_Prompt."d;
Output;

Format Weekend Date.;

Run;


Data Work.SSW;
	Set Cartel.preagent_tel;
	Keep Week_end_dt EVENT_DT PROGRAMME_NM SERVICE_NM CLUSTER CALLS_ANSWERED_CNT
		CALLS_ABANDONED_CNT TOTAL_ANSWER_TM MBL
		;
	Where week_end_dt = "&Weekend."d
		and CALL_TYPE = 'External'
		and PROGRAMME_NM IN ("1.1 SSW");

	
Run;

This is the code I have used so far but I get the following error

 

42          Where week_end_dt = "&Weekend."d
WARNING: Apparent symbolic reference WEEKEND not resolved.
ERROR: Invalid date/time/datetime constant "&Weekend."d.
43           and CALL_TYPE = 'External'
44           and PROGRAMME_NM IN ("1.1 SSW");
ERROR: Syntax error while parsing WHERE clause.

 

 

Hi All,

 

I have several programs, in onme project, which use a stored process for which I use to enter the date I need data for. I run these programs consecutively and I have to select the date for each program (Same date for each program) 

 

EG. Where week_end_dt = "&Excel_Date_Prompt."d

 

Is there a way of writing a macro that I can use that I only enter the date from the stored process and it then filters to the other programs?

 

Hope this makes sense.

 

Regards

6 REPLIES 6
LaurieF
Barite | Level 11

The main problem you have here is the internal and external representation of dates in SAS. When you refer to "17dec2016"d, internally it's stored as 20805. So when you try to process "20805"d, SAS doesn't like it.  The other reason SAS doesn't like it is because you're referring to weekend which is never populated, either as a SAS or macro variable!

 

So, if the answer to your prompt is 17dec2016, then you probably should keep it like that.

 

If that's the case, get rid of your first data step, and modify your second one to:

 

Data Work.SSW;
	Set Cartel.preagent_tel;
	Keep Week_end_dt EVENT_DT PROGRAMME_NM SERVICE_NM CLUSTER CALLS_ANSWERED_CNT
		CALLS_ABANDONED_CNT TOTAL_ANSWER_TM MBL
		;
	Where week_end_dt = "&excel_date_prompt"d
		and CALL_TYPE = 'External'
		and PROGRAMME_NM IN ("1.1 SSW");

	
Run;

The value of excel_date_prompt will carry across all the steps in your project.

 

If, BTW, the prompt is returning 20805, make it:

Where week_end_dt = &excel_date_prompt

 

DME790
Pyrite | Level 9

Thanks Laurie

Data Work.SSW;
	Set Cartel.preagent_telephony;
	Keep Week_end_dt EVENT_DT PROGRAMME_NM SERVICE_NM CLUSTER CALLS_ANSWERED_CNT
		CALLS_ABANDONED_CNT TOTAL_ANSWER_TM MBL
		;
	Where week_end_dt = "&Excel_Date_Prompt."d
		and CALL_TYPE = 'External'
		and PROGRAMME_NM IN ("1.1 SSW");
	
Run;

This is my working code. The problem I have is when I run all the programs in the project each one loads the prompt and I have to select the date again  - I would like to stop the Prompt loading for each program. I have used the

Capture.PNG

 

But this doesn't load any data for me.

LaurieF
Barite | Level 11

Ah - sorry. I hope someone else with EG access can help - SAS Studio doesn't have that functionality, and it's been ages since I used EG prompts.

TomKari
Onyx | Level 15

First, a question. You say you're using a "stored process" to set the date, but it looks like you're just using an EG prompt. I'm assuming the latter is the case.

 

What I think you're describing works perfectly for me:

1. Set up a prompt using Prompt Manager.

2. Create three programs that use the macro variable created by the prompt.

3. In the Program Properties for the first program, associate it with the prompt.

 

When I run Program 1, it asks for the prompt, and the macro variable in the program has the correct value.

When I run Program2 and Program3, no prompt, and the macro variable in the program has the correct value.

 

Is that what you're after?

  Tom

DME790
Pyrite | Level 9

Hi Tom,

 

My apologies, yes I'm using a prompt to get the date - the stored process is to get the data to Excel.

 

What you have decscribe is what I'm after - When I run the stored process in Excel I the prompt is shown for each program.

 

Is there antway around this?

 

Cheers

 

Dean

TomKari
Onyx | Level 15

Okay, now it's starting to become clearer.

 

If I understand correctly, you are creating a stored process so that you can run it from Excel. And the issue is to have the value returned from the prompt available for more than one program.

 

My next question. Are you creating Program1, Program2, and Program3 as three separate stored processes, and running them one at a time from Excel?

 

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