Getting a value from a Text Entry Control in SAS AF App for WHERE statement

[deleted_user]

I have a Text Entry Control where the user is entering a date. The control is formatted as a numeric with the mmddyy8. format and mmddyy10. informat (same formats as the variable in the data set I am using to subset). I am trying to subset a data set using the date submitted by the user with a WHERE statement. The date appears in the log as the SAS date (or number) but with 0 observations found. There should be about 30. I have been trying all sorts of things to get the code to work and have included the current version below: (I am sure it is something very minor but it just isn't coming to me!) Thanks for the help!

rc=rc;

INIT:
RETURN;

DICGENREPORTS:

SUBMIT CONTINUE;

LIBNAME CMVFUP 'K:\CMVFollowupData';

DATA A; SET CMVFUP.VISITS;
WHERE VISITDATE NE .;

DATA B; SET A;

WHERE VISITDATE GE &TECVTDATE; *SAS sees the date as the SAS date (or number) but it returns 0 observations. It should be returning about 30.

[deleted_user]

print out value of &TECVTDATE and see how it is resolved in submit block.

[deleted_user]

Seems SAS is seeing the value entered as the SAS date and will print it out as such once I give it a temp variable to be assinged to but when I format it out it just formats the exact number that is show it doesn't convert it to the appropriate date. Does that make sense? I have included the program as well as an example of the output:
rc=rc;

INIT:
RETURN;

DICGENREPORTS:


SUBMIT CONTINUE;

LIBNAME CMVFUP 'K:\CMVFollowupData';

DATA A; SET CMVFUP.VISITS;
date_1=&tecvtdate; *reads 24617 when 01/01/2010 is entered into the text entry control
format date_1 mmddyy8.;
informat date_1 mmddyy10.;


proc print; var studyid visitdate date_1 visitmonth; run;

endsubmit;

return;

OUTPUT:

Obs studyid visitdate date_1 visitmonth

518 66018 01/08/10 05/26/27 18
519 66021 10/29/08 05/26/27 0
520 66022 12/10/08 05/26/27 0
521 66022 04/24/09 05/26/27 7

[Flip]

I would suggest you set options MPRINT; or put a %put =&tecvtdate; right before the where, to make sure that =&tecvtdate resolves to a number. The format is not important.

[Cynthia_sas]

Hi:
Without getting into macro issues or AF issues, let's just look at successful WHERE statements. The informat only applies to how a date is ENTERED into a field or in Edit mode; the format only applies to how a date is DISPLAYED. Neither the informat nor the format applies to a WHERE statement.

If you run the program below, you will understand why it is important to understand how date and date constants work in a WHERE statement before you provide a value for the macro variable that's going to be used in your WHERE. You will see output from #1, #2 and #4, but if your macro variable resolves to anything like what is shown in #3, you will get 0 observations being selected, because the date value of 11/15/1950 is NOT how the date is internally stored.

Finally, report #5 shows a macro variable used in the WHERE statement.

cynthia
[pre]
data bday;
infile datalines;
input name $ bday : mmddyy10.;
return;
datalines;
alan 11/15/1950
barb 11/23/1989
carl 11/15/1950
dana 08/23/1951
edwin 09/15/1988
;
run;

ods listing;
proc print data=bday;
title 'bday date values without formats';
run;

proc print data=bday;
title 'bday using format';
format bday mmddyy10.;
run;

proc print data=bday;
title '1) bday with GOOD where statement';
where bday = '15NOV1950'd;
format bday mmddyy10.;
run;

proc print data=bday;
title '2) bday with GOOD where statement';
where bday = -3334;
format bday mmddyy10.;
run;

proc print data=bday;
title '3) bday with BAD where statement';
where bday = 11/15/1950;
format bday mmddyy10.;
run;

proc print data=bday;
title '4) bday with GOOD where statement';
where put(bday,mmddyy10.) = '11/15/1950';
format bday mmddyy10.;
run;


%let wantbday = 15Nov1950;
proc print data=bday;
title '5) bday with GOOD where statement and macro variable';
where bday = "&wantbday"d;
format bday mmddyy10.;
run;

[/pre]

[deleted_user]

The following is the complete WHERE clause that works fine outside of the AF application when I am inputting a specific date using the MDY function. However, this is what I am getting when I try to insert the value entered into the text entry control on the application. Thanks for all the suggestions. I have several applications that have similar fields on them that work fine, I just can't figure out why this isn't working.

LOG:
LIBNAME CMVFUP 'K:\CMVFollowupData';
1673 data one;
1674 set cmvfup.visits;
1675 where (visitdate gt "24617"d and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine
1675! ne .);
ERROR: Invalid date/time/datetime constant "24617"d.
ERROR: Syntax error while parsing WHERE clause.

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.ONE may be incomplete. When this step was stopped there were 0
observations and 41 variables.
WARNING: Data set WORK.ONE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

Program:

rc=rc;

INIT:
RETURN;

DICGENREPORTS:



SUBMIT CONTINUE;

LIBNAME CMVFUP 'K:\CMVFollowupData';


data one; set cmvfup.visits;

where (visitdate gt "&tecvtdate"d and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine ne .);

[Flip]

where (visitdate gt "24617"d and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine
Should be
where (visitdate gt 24617 and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine

[deleted_user]

When I leave it as just
where (visitdate gt 24617 and visitmonth=0)
which is how I thought it should be also, I end up with 0 observations when there should be about 30 based on running it outside the AF application.

Log:
LIBNAME CMVFUP 'K:\CMVFollowupData';
1733 data one;
1734 set cmvfup.visits;
1735 where (visitdate gt 24617 and visitmonth=0) and (cmvresultsaliva ne . or cmvresulturine ne
1735! .);

NOTE: There were 0 observations read from the data set CMVFUP.VISITS.
WHERE (visitdate>24617) and ((cmvresultsaliva not = .) or (cmvresulturine not = .)) and
(visitmonth=0);
NOTE: The data set WORK.ONE has 0 observations and 41 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.00 seconds

[Flip]

That could be because 24617 is May26, 2027
Jan 01, 2010 is 18263

[deleted_user]

I have the format on the text entry control set as a numeric with a format of mmddyy8. and informat of mmddyy10. and when I enter the date in the field it appears to be correct (01/01/10).

[Flip]

Run;

data _null_;
x = 24617;
put(x, date9.);
x = 18263;
put(x, date9.);
run;

[deleted_user]

This is what I get:


data _null_;
1876 x = 24617;
1877 put(x, date9.);
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, ), -, :, [, _ALL_,
_CHARACTER_, _CHAR_, _NUMERIC_, {.

ERROR 76-322: Syntax error, statement will be ignored.

1878 x = 18263;
1879 put(x, date9.);
-
22
76
ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, ), -, :, [, _ALL_,
_CHARACTER_, _CHAR_, _NUMERIC_, {.

ERROR 76-322: Syntax error, statement will be ignored.

1880 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

[Cynthia_sas]

You cannot use PUT with a number that represents a date...PUT creates a CHARACTER variable. That would be wrong, especially since the date is stored as a number....you cannot have a WHERE statement with mis-matched types (as in comparing a character string to a date value).

If you are getting a TEXT value of 24617 and you want to turn that text string into the NUMBER 24617, then the INPUT function is what turns a character string into a NUMBER.

cynthia

[Cynthia_sas]

Hi:
I cannot think of any syntax situation where this should work:
where date = "number"d;

for example, consider this SAS log which extends my previously posted example:
[pre]
74
75 proc print data=bday;
76 title '6) Another BAD where statement';
77 where bday = "-3334"d;
ERROR: Invalid date/time/datetime constant "-3334"d.
ERROR: Syntax error while parsing WHERE clause.
78 format bday mmddyy10.;
79 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

[/pre]

I know that -3334 is the internal stored value for 11/15/1950. However, the construct: "-3334"d is just plain wrong, because it is an invalid form of a date or datetime constant (as it says in the note). I suspect that there is some other reason that your WHERE statement is failing when you remove the "..."d from around your number 24617 -- possibly the combination of NOT and AND and OR conditions is not resulting in the query results you expect.

If you continue to have issues, I would recommend that you open a track with Tech Support. They can look at your actual data, your actual AF code and make recommendations about solutions.

cynthia

[deleted_user]

i suspect the issue is were date text entry converted to &tecvtdate. debug that portion of scl...