BookmarkSubscribeRSS Feed
Ram4sas
Fluorite | Level 6


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,

3 REPLIES 3
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Ram4sas
Fluorite | Level 6

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..

Quentin
Super User

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?


BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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