BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
15 REPLIES 15
deleted_user
Not applicable
print out value of &TECVTDATE and see how it is resolved in submit block.
deleted_user
Not applicable
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
Fluorite | Level 6
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
SAS Super FREQ
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
Not applicable
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
Fluorite | Level 6
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
Not applicable
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
Fluorite | Level 6
That could be because 24617 is May26, 2027
Jan 01, 2010 is 18263
deleted_user
Not applicable
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
Fluorite | Level 6
Run;

data _null_;
x = 24617;
put(x, date9.);
x = 18263;
put(x, date9.);
run;
deleted_user
Not applicable
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
SAS Super FREQ
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
SAS Super FREQ
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
Not applicable
i suspect the issue is were date text entry converted to &tecvtdate. debug that portion of scl...

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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