BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
OscarBoots1
Quartz | Level 8

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
OscarBoots1
Quartz | Level 8

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.

 

 

View solution in original post

14 REPLIES 14
Reeza
Super User

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

OscarBoots1
Quartz | Level 8

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
Super User

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

 

today='24July2017'd;
prev_date = intnx('weekday', today, -1);
OscarBoots1
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Onyx | Level 15

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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

OscarBoots1
Quartz | Level 8

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.

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
  • 14 replies
  • 5902 views
  • 1 like
  • 3 in conversation