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 */
FROM_DATE = ("&frdate");
CREATE TABLE trans_file AS SELECT DISTINCT TRANSACTION_HISTORY.DOCUMENT FORMAT=$63.,
datepart(TRANSACTION_HISTORY.TRANSACTION_DATE) FORMAT=date9. as TRANS_DATE,
sum (TRANSACTION_HISTORY.TRANSACTION_AMOUNT) as TRANS_AMT,
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 ;
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.
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:
d_t = '15nov1950:23:45'dt;
date = datepart(d_t);
put "The date is";
put date worddate.;
put date mmddyy10.;
put date monyy5.;
Results in the following in the SAS Log:
d_t=-287972100 date=-3334 _ERROR_=0 _N_=1
The date is
November 15, 1950
NOTE: The data set WORK.TESTDATE has 1 observations and 2 variables.
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:
where datepart(d_t) = -3334;
where datepart(d_t) = '15nov1950'd;
%let fromdate = -3334;
where datepart(d_t) = &fromdate;
Have you checked that the data did not change and that there ARE dates that exactly equal the from date?