Multiple programs using multiple Stored Process which always use the same date

[DME790]

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

[LaurieF]

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]

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

But this doesn't load any data for me.

[LaurieF]

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]

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]

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]

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?