Can not resolve date parameter

[deleted_user]

I am having problem with date parameter not resolving when I tried bringing it as string . I used date parameter in proc sql in where clause . It brings no rows. Following is the code I used. Please help.


/* CONVERTs date parm as ''string data type FROM PARAMETER SCREEN 01/09/2008 */
DATA temp1;
FROM_DATE = ("&frdate");
TO_DATE =("&todate");
SASFROMDT=INPUT("&frdate",MMDDYY10.);
SASTODT=INPUT("&todate",MMDDYY10.);

CALL SYMPUT('SASFRDATE',SASFROMDT);
CALL SYMPUT('SASTODATE',SASTODT);
put SASFROMDT= SASTODT= ;
run;
%put &frdate &todate &SASFRDATE &SASTODATE;

PROC SQL;
CREATE TABLE trans_file AS SELECT DISTINCT TRANSACTION_HISTORY.DOCUMENT FORMAT=$63.,
datepart(TRANSACTION_HISTORY.TRANSACTION_DATE) FORMAT=date9. as TRANS_DATE,
TRANSACTION_HISTORY.TRANSACTION_DESC FORMAT=$255.,
TRANSACTION_HISTORY.ITEM,
TRANSACTION_HISTORY.SEQUENCE_NUMBER,
TRANSACTION_HISTORY.FIELD_CODE FORMAT=$63.,
TRANSACTION_HISTORY.FISCAL_YEAR FORMAT=$63.,
sum (TRANSACTION_HISTORY.TRANSACTION_AMOUNT) as TRANS_AMT,
TRANSACTION_HISTORY.ACCOUNT FORMAT=$63.
FROM ODSP.TRANSACTION_HISTORY AS TRANSACTION_HISTORY
WHERE datepart(TRANSACTION_HISTORY.TRANSACTION_DATE) = &SASFRDATE
GROUP BY VENDOR_ID,ACCOUNT
ORDER BY TRANSACTION_HISTORY.FUND ,TRANSACTION_HISTORY.ACCOUNT ;
QUIT;

[deleted_user]

Formats and informats only apply to printing and inputing data.
SAS stores data as either a character string or as a number.
I expect that TRANSACTION_DATE is a numeric that has a date format associated with it to make it a "date type" value.

So, you need the actual numeric value to use for the comparison.

If you had been using "pass-through" to connect to an Oracle or MS SQL Server or other RDMS, then you would need to convert the where value to a text value inclosed in quotes with the correct format for the RDMS.

[deleted_user]

Hi Chuck,
The same code worked in EG 3.0. Anyway, how do I convert &SASFRDATE to text format . can you please give sample code?

[Cynthia_sas]

Hi:
The DATEPART function returns a number. So, in the program below, the DATE portion of the datetime variable D_T is -3334, the internal representation of Nov 15, 1950.

Consider this program:
[pre]
data testdate;
d_t = '15nov1950:23:45'dt;
date = datepart(d_t);
put _all_;
put "The date is";
put date worddate.;
put date mmddyy10.;
put date monyy5.;
run;
[/pre]

Results in the following in the SAS Log:
[pre]
d_t=-287972100 date=-3334 _ERROR_=0 _N_=1
The date is
November 15, 1950
11/15/1950
NOV50
NOTE: The data set WORK.TESTDATE has 1 observations and 2 variables.

[/pre]

Since your WHERE clause is using the DATEPART function on your date variable, then it is appropriate for you to specify a number for comparison...you either have to do that or specify a date constant.

So, for example, a WHERE statement/clause that used the above datetime variable could be built this way:
[pre]
where datepart(d_t) = -3334;
where datepart(d_t) = '15nov1950'd;
OR
%let fromdate = -3334;
where datepart(d_t) = &fromdate;
[/pre]

Have you checked that the data did not change and that there ARE dates that exactly equal the from date?

cynthia