Add a Date field to table using a Prompt or Proc

[OscarBoots1]

Hi Forum,

I get a dataset each day for the previous days results which on Mondays is for last Friday.

I need to add the Date that the data is for so it's usually yesterday unless it's Monday as I've stated.

I want the User to be able to add the relevant date preferably using the 'Datepicker' tool if possible.

I can't find any tips on code that would open & run the Datepicker, accept the date selected & add that Date to the table.

Any suggests welcome.

[Reeza]

You would create a prompt. Then do a query and add the prompt value into the table - prompts are macro variables.

[OscarBoots1]

Thanks Reeza,

I did try something like that but the Prompt Date values needed to match the Date on the daily table.

As there is no date as yet, it wouldn't give any results.

I just want a 'DatePicker' to pop up (like a prompt), I select a date (Yesterday or last Friday) & have that Date added to all rows of my daily report data. 

Thanks

[Reeza]

Do you have to prompt? Can you use INTNX with the WEEKDAY option?

today='24July2017'd;
prev_date = intnx('weekday', today, -1);

[OscarBoots1]

Ideally, a prompt would be better as in 'more user friendly'.

It would also allow any date value to be easily added.

I need to accomodate dates for the previous working day which could change with weekends, public hildays etc etc.

[Reeza]

I'm not sure asking users to do anything is considered user friendly. 

You can also use the file create/modify date would be my other suggestion. 

I think you can also build prompts to pull from a table/view. So you may be able to build a view with the last 5 dates or something along those lines and work with that. Or you could write a custom calendar to account for public holidays and such and use a custom interval. There are examples of the latter on here, the former you need EG to test out.

[OscarBoots1]

Thanks Reeza, I'll have a look at those options.

My point about 'User friendly' is that a 'Datepicker' is faster & neater than asking a user to add a date into the code.

Cheers

[TomKari]

I think I see where you're trying to go, but I don't think you can put that much logic into the default value for a date prompt. All I can see to do is to set the default to "yesterday", and your user will need to know to use the date picker to modify this date in the case of a previous Friday, or adjusting for a holiday, etc.

Tom

[OscarBoots1]

That's right Tom & what I need is to use the Prompt to insert the date.

How can I use the Datepicker prompt to insert a date into a table where there isn't a matching value?

I mean the report will have data from yesterday & further in the past, my new daily data will have todays date.

So I can't use a prompt to match dates between the two.

Does the Datepicker have the functionality to just add a selected value & insert it into a table??

Thanks

[OscarBoots1]

I think I've found the best solution from a previous post.

It uses a prompt value in a calculated field.

https://communities.sas.com/t5/SAS-Enterprise-Guide/Using-a-prompt-value-in-a-computed-column/td-p/1...

I won't have access to EG until Monday, so my question is "How does the user input a date for the Datepicker prompt?"

Does the prompt value (example &promptname) make the prompt dialogue box pop up so the User can enter a Date?

Can anyone clarify here?

Thanks

[Reeza]

Are you asking how to create a user prompt?

Typically if you're using datepicker they can select off a calender, but it does depend on how you design the prompt. 

---

@OscarBoots1 wrote:

I think I've found the best solution from a previous post.

It uses a prompt value in a calculated field.

https://communities.sas.com/t5/SAS-Enterprise-Guide/Using-a-prompt-value-in-a-computed-column/td-p/1...

I won't have access to EG until Monday, so my question is "How does the user input a date for the Datepicker prompt?"

Does the prompt value (example &promptname) make the prompt dialogue box pop up so the User can enter a Date?

Can anyone clarify here?

Thanks

---

[OscarBoots1]

Hi Reeza,

That's a possible option to get a User to enter a Date using a Date Calendar Prompt (What I call a 'Datepicker').

When I read the other post in the link, I thought an option could be to use the prompt value in a calculated field.

Let's say that I create a prompt in EG called 'Report_Date' and give it a range of values from a table I create where the User selects just one date.

That prompt value is held as &Report_Date

I have a field in my existing table called DATE_ID which needs a date added for the actual Report date. 

Next I use the Calculated Value feature to create a date value in a table called Report_Date that equals the &Report_Date value.

The Caculated field would be something like this;

CASE WHEN &Report_Date IS NOT NULL THEN &Report_Date END

AS DATE_ID  

Any suggestions?

[OscarBoots1 · Accepted solution]

I found the answer.

My project had some daily data & I added a blank date field as a calculated column using ''d as the format.

This is where I wanted to add the selected date from the 'Datepicker' prompt.

First step is to create the Prompt, then 'Register' the Prompt by right clicking on the Query Builder & selecting Properties >> Prompts & select the Prompt you want.

Next I added a Case Statement that just said If the date field of the table was blank, then insert the Prompt date.

CASE

WHEN t1.DATE_ID IS NULL THEN

"&Report_Date"d  END

NOTE the double quotes around the Prompt name.

Now when the Project is run, a datepicker pops up, the User selects a date & it appears in the first row of the table.

[Reeza]

You were able to restrict the 'date picker' to the values you wanted? 

[OscarBoots1]

Hi Reeza,

When I created the Prompt, I used an Excel file with the date ranges I needed & used 'Get values' to enter these as the dates I wanted.

When the Prompt appears at the start of running the Project I get a dialogue box where I can either select a date from the list or use the 'Today', 'Yesterday' 'N days from Now' etc etc to get the date I need.

Thanks for your suggestions.