BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
3 REPLIES 3
deleted_user
Not applicable
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
Not applicable
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
SAS Super FREQ
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

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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