topic Re: Why stored process dynamic filter not retrive data from stored process temp table in SAS Enterprise Guide

Why stored process dynamic filter not retrive data from stored process temp table

Ram4sas — Thu, 08 May 2014 09:53:19 GMT

I have below stored process to do below task...

1.which read data from DB table to

2.create temp table with new derived(date conversion variables)

3.create another table with complete where condition for Dynamic filter.

Libname source DB server user=xxxxx pw=xxxxx ;.

libanme sp_result 'c:/programFiles/data';

data sp_result.temp;

set source.temp;

refresh_date=Input(r_date,Informat);

refresh_mon=Input(r_month,Informat);

run;

create table sp_result.Final as

select * from sp_result.temp

where refresh_mon eq &refresh_mon and refresh_date eq &refresh_date;/* here &refresh_mon and &refresh_date is dynamic prompts set up done.

Quit;

when I run this code, the prompts are prompting and displays single date values which were old dates.

but after data refresh done with new refresh month and date , the newly added refresh mon, refresh_date details not retrieve/available in dynamic prompt to select.

why this strange behavior with Stored Process occurring with my code? is there some thing I am missing to add.

THanks,

Re: Why stored process dynamic filter not retrive data from stored process temp table

Quentin — Thu, 08 May 2014 10:02:58 GMT

So you are using sp_result.Final as the source data for your dynamic prompts, right?

When you registered that table in the metadata, did the variables refresh_date and and refresh_mon exist?

Sounds like these were never registered. I would suggest you re-register the table (update metadata). And then take a look through EG to make sure you can see these columns.

Re: Why stored process dynamic filter not retrive data from stored process temp table

Ram4sas — Thu, 08 May 2014 10:19:56 GMT

Hi Quentin,

not sp_result.Final is source, sp_result.Temp is source which created in previous data step with new defined variables.

sp_result.Final is output table.

yes, after I ran data step, I had registered sp_result.Temp table in metadata, that's why I would have been able to set up prompt to refresh_date and refresh_mon from sp_result.temp table and able to get old date/month values for filter.

adding step one more time with udpations...

1.which read data from DB table to

2.create temp table with new derived(date conversion variables), then registered temp table in Metadata.

3.create another table with complete where condition for Dynamic filter.

I can see month and dates details in promt drop down which were there when the stp created.

yesterday data been refreshed, the new month and date not being pulled and avaible in Dynamic prompt to select.

Thanks..

Re: Why stored process dynamic filter not retrive data from stored process temp table

Quentin — Thu, 08 May 2014 12:40:46 GMT

Oh so sp_result.Temp is the source data for your dynamic prompt, right?

And it's working now so that when you run the stored proecess, you see the prompt values from that table. The the problem is you don't see the new values added yesterday (i.e. new records you added yestereday to sp_result.Temp). Is that right?

If you PROC PRINT sp_result.Temp do you see the new records that were added yesterday?