topic Re: selecting the contents of the table after selecting the date in SAS Programming

selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 13:59:51 GMT

I would like to use the box to select the date, e.g. June 30, and then I would like to create a table from the library credit with two columns where Default is 1

This is my code, but nothing happens, the window does not appear, but with the dates from the initial start of the program. How to replace the code so that after pressing the runes, the date selection window will open and then create a table with these two items. Where is the mistake

%let gv_dateDEF=%sysevalf(%bquote('&data.'d));
%let yy=%sysfunc( YEAR(&gv_dateDEF.),z4.);
%let mm=%sysfunc(MONTH(&gv_dateDEF.),z2.);
%let dd=%sysfunc( DAY(&gv_dateDEF.),z2.);
%let gv_tbl_date=%eval(%str(&yy&mm&dd));
proc sql;
create table NewDef as
select
spr_NRB,
where(spr_DEFAULT = 1)
from credit;

Re: selecting the contents of the table after selecting the date

Kurt_Bremser — Thu, 14 Oct 2021 14:02:42 GMT

Macro triggers will not be resolved when inside single quotes.

Re: selecting the contents of the table after selecting the date

Kurt_Bremser — Thu, 14 Oct 2021 14:06:18 GMT

And your whole conversion can be simplified to this:

%let data = 30jun2021;
%let gv_tbl_date = %sysfunc(inputn(&data.,date9.),yymmddn8.);
%put &=gv_tbl_date.;

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 14:06:51 GMT

are you mean change to : %let gv_dateDEF=%sysevalf(%bquote("&data."d)); ?

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 14:27:13 GMT

Thanks a lot! So if i Good understood if i have date in table like 2021-08-31 i should change to yymmddn10. And date to %let date = 2021-08-31? And for example if i have many date in my dataset i can add where date=gv_tbl_date

Re: selecting the contents of the table after selecting the date

Kurt_Bremser — Thu, 14 Oct 2021 14:40:09 GMT

Your code as posted was designed to create a date string of 8 digits with no delimiters. So I did that.

To get 10 digits with hyphens as delimiters, use the YYMMDD10. format.

And I used the DATE9. informat for the initial string because it looks like you have that at this moment. Macro variables created by user prompts of type date will look like that.

But if you need this to use as a cutoff for SAS dates, apply no format at all, and use the raw value (number of days since 1960-01-01).

So please show what you really get initially in &data, and what you intend to do with the macro variable you create.

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 15:44:36 GMT

When i run your code still nothing happend. No bugs , no information. Above % let date I also have another code above. Maybe it is a matter that at the start of all code I run a macro with data? However, here also needs to open another date picker. For example, now I am doing a report for September, but in this table I have to take the dates for June

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 15:50:57 GMT

I would like to open such a window ( inputox)

Re: selecting the contents of the table after selecting the date

Kurt_Bremser — Thu, 14 Oct 2021 15:55:52 GMT

Inputs for dynamic code are done in Enterprise Guide by defining user prompts. Such user prompts, when defined as type date, will deliver the resulting date in DATE9 format in a macro variable that has the name of the prompt.

So what are you trying to do? Please give us the bigger picture.

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 16:10:09 GMT

I would like to create such an inputbox, but I do not know how, wich one after clicking on the program, a window with a date selection would appear. and to execute your macro after selecting the date

Re: selecting the contents of the table after selecting the date

Kurt_Bremser — Thu, 14 Oct 2021 16:15:34 GMT

In Enterprise Guide, you create such windows as user prompts; the resulting code + prompts can then be converted to a stored process to be run on the stored process server, either from EG or through the web interface.

In Display Manager, you can use input commands, but I haven't worked with DM in a very long time.

Which of the interfaces for the SAS system are you using? Display Manager, Enterprise Guide, SAS Studio?

Re: selecting the contents of the table after selecting the date

Reeza — Thu, 14 Oct 2021 16:21:24 GMT

Is Date in mart_credit a SAS date? Numeric with a format?
What is the name of the prompt with the date and how does it resolve? You can check it with the following:

%put &promptVariable.;

If the date is numeric with a date format and promptVariableValue looks like '01Jun2021' then your code would look like:

where date = "&promptVariable"d;

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 16:28:29 GMT

I HAVE CREATED A PROMPT CORE DATE BUT HOW TO ASSIGN THIS TO A PROGRAM ?

Re: selecting the contents of the table after selecting the date

Reeza — Thu, 14 Oct 2021 16:32:03 GMT

where date = "&corepdate"d;

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 16:40:32 GMT

But how to run prompt to select needed date? How to assigned this prompt to some button / programs

Re: selecting the contents of the table after selecting the date

Kurt_Bremser — Thu, 14 Oct 2021 16:45:38 GMT

WHICH.

INTERFACE.

DO.

YOU.

USE?

As long as you refrain from really answering our questions, we will not be able to help you.

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 16:47:47 GMT

Sas enterprise guide sas eg im using

Re: selecting the contents of the table after selecting the date

Reeza — Thu, 14 Oct 2021 17:07:34 GMT

This then?
https://video.sas.com/detail/video/3829767505001/create-and-use-prompts-in-queries-and-tasks

Or a written reference if you prefer that:

https://support.sas.com/resources/papers/proceedings13/028-2013.pdf

Re: selecting the contents of the table after selecting the date

Gieorgie — Thu, 14 Oct 2021 17:58:38 GMT

Ok, I created a prompt where I can select a list from the hints, how to enter a macro in the Program icon that will load me the date from the prom?

Re: selecting the contents of the table after selecting the date

Reeza — Thu, 14 Oct 2021 18:02:21 GMT

Did you look at the video or paper referenced? It has instructions on how to create a prompt and then use it in your processes.