BookmarkSubscribeRSS Feed
fredbell
Fluorite | Level 6
Hello

Does or should this covert the variable to Date because when i look at the properties of the dataset the type still says number not date?

data Calls_Handled (keep=Date agentcallshandled level_7_id);
set Calls_Handled_Temp ;
Date=input(ReportTime, anydtdtm22.);*this converts it to datetime variable;
where ReportTime >= &MTDfromdate and ReportTime < &MTDTodate;
format Date datetime9.;
run;


format changes to Datetime9

Fred
6 REPLIES 6
ArtC
Rhodochrosite | Level 12
SAS date, time, and datetime values are stored in numeric variables. The value stored is a displacement value either in days (date) or seconds (time and datetime). Formats, informats (such as anydtdtm), and functions are applied to these numeric values to create something that is readable in our calendar or clock system.
Ksharp
Super User
Hi.
I am not sure.But the code is worked fine for me.

[pre]
data _null_;
date=input('12aug2009:10:20:32',anydtdtm22.);
format date datetime9.;
put date=;
run;
[/pre]


Ksharp
fredbell
Fluorite | Level 6
Thanks it was the put that made it work, that is the part i was lacking.

Fred
polingjw
Quartz | Level 8
Also, be careful about the variable you are using in your where statement:

where ReportTime >= &MTDfromdate and ReportTime < &MTDTodate;

ReportTime is still a character variable so SAS will probably not perform the type of comparison that you are looking for. You might want to change this statement to an if statement that makes the comparison based on the Date variable instead.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Depending on your input file size, it may be more efficient to use the INPUT function on the WHERE statement and also use date or datetime literal strings when you want SAS to treat / convert a character string to a SAS DATE or DATETIME represented value.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

using date datetime literals site:sas.com
Cynthia_sas
SAS Super FREQ
Hi:
Thanks so much for mentioning this "ReportTime is still a character variable so SAS will probably not perform the type of comparison that you are looking for".

I was hoping someone would also ask or point out that IF ReportTime is a character variable, then the WHERE clause would expect that ReportTime was being compared to a quoted character string. Since we were never shown the values for &MTDfromdate and &MTDTodate, that led me to wonder whether the OP actually understood the difference between using character strings in comparisons and using numeric values in comparisons.

Also, I sort of wondered why the BETWEEN operator wasn't being used since it seems perfect for this.

But back to the &MTDfromdate and &MTDtodate issue. At some point, I expected to hear of an error message like this:
[pre]
ERROR: Expression using greater than or equal (>=) has components that are of different data types.
[/pre]

...or to discover that the WHERE clause was not selecting the correct record set.

Consider this data:
[pre]
data bday;
infile datalines dlm=' ';
input name $ bday : mmddyy10. charbday $;
unfmtbday = bday;
format bday mmddyy10.;
return;
datalines;
alan 11/15/1950 15Nov50
bob 05/09/1955 09May55
cathy 11/19/1966 19Nov66
dave 11/29/1984 29Nov84
eliza 05/24/1936 24May36
fran 11/15/1950 15Nov50
george 11/15/1950 15Nov50
;
run;

proc print data=bday;
title 'What does data look like';
run;
[/pre]

Note that there is a character string that represents the birthday and 2 numeric variables that represent birthday: BDAY is the numeric birthday value formatted with the MMDDYY10. format; UNFMTBDAY is the numeric birthday value which is not formatted in order to see the internally stored number; and CHARBDAY is a character string that represents the birthday.
[pre]
What does data look like

Obs name bday charbday unfmtbday
1 alan 11/15/1950 15Nov50 -3334
2 bob 05/09/1955 09May55 -1698
3 cathy 11/19/1966 19Nov66 2514
4 dave 11/29/1984 29Nov84 9099
5 eliza 05/24/1936 24May36 -8622
6 fran 11/15/1950 15Nov50 -3334
7 george 11/15/1950 15Nov50 -3334
[/pre]

Now, given that data the program below uses some macro variables to select observations in PROC SQL. Here are the macro variables:
[pre]
%let usenum = -3334;
%let useconstant = 15Nov50;
%let startchar = 15Nov50;
%let endchar = 31May55;
[/pre]

Next, the program has 7 different PROC SQL steps, each with a different WHERE clause, as follows:
[pre]
a) where bday = "&useconstant"d;
b) where bday = &usenum;
c1) where bday between "&startchar"d and "&endchar"d;
c2) where bday GE "&startchar"d and bday LT "&endchar"d;
d) where charbday = "15Nov50";
e) where charbday GE "&startchar" and charbday LT "&endchar";
f) where bday GE "&startchar" and charbday LT "&endchar";
[/pre]

A, B, C1, and C2 will all work becasue they are correctly comparing the numeric value for BDAY to a correctly specified date constant or number. D will work because the character variable CHARBDAY is being compared to only 1 character value for selection. E will work but will not select the correct rows because the character value for CHARBDAY is being tested as a CHARACTER string (not against real numeric values that represent dates). F will not work because the numeric value for BDAY is being compared to 2 character strings and SQL will not allow you to have 2 different data types in a comparison.

For comparison purposes, the output from C1, C2 and E are shown below (with **** to divide the 3 outputs):
[pre]

c1) Use BETWEEN with macro var for comparison
WHERE BDAY BETWEEN -3334 and -1676 (as numeric)

name bday charbday unfmtbday
-----------------------------------------
alan 11/15/1950 15Nov50 -3334
bob 05/09/1955 09May55 -1698
fran 11/15/1950 15Nov50 -3334
george 11/15/1950 15Nov50 -3334

*******************************************

c2) Use other operators with macro var for comparison
where bday GE -3334 and bday LT -1676 (as numeric)

name bday charbday unfmtbday
-----------------------------------------
alan 11/15/1950 15Nov50 -3334
bob 05/09/1955 09May55 -1698
fran 11/15/1950 15Nov50 -3334
george 11/15/1950 15Nov50 -3334

*******************************************

e) Try Character variables in the WHERE with diff condition
where charbday GE 15Nov50 and charbday LT 31May55 (as character)
This will NOT work

name bday charbday unfmtbday
-----------------------------------------
alan 11/15/1950 15Nov50 -3334
cathy 11/19/1966 19Nov66 2514
dave 11/29/1984 29Nov84 9099
eliza 05/24/1936 24May36 -8622
fran 11/15/1950 15Nov50 -3334
george 11/15/1950 15Nov50 -3334
[/pre]

I used date values and data constants to simplify the program. However, SAS date/time values are still just internally stored numbers -- they're just a lot bigger, representing the number of seconds since midnight on Jan 1, 1960 instead of the number of days from Jan 1, 1960. Since we never saw the whole program or the macro variable values, I can't say for sure whether the WHERE clause in PROC SQL worked correctly. We still don't know for sure that ReportTime actually -is- a character variable.

Every once in a while, it helps to make sure that we are all on the same wavelength as far as macro variables and date (or date/time) values in comparisons. The SQL steps are included below.

cynthia
[pre]
%let usenum = -3334;
%let useconstant = 15Nov50;
%let startchar = 15Nov50;
%let endchar = 31May55;

title 'a) use macro variable with date constant';
proc sql;
select *
from work.bday
where bday = "&useconstant"d;
quit;

title 'b) use macro variable with date number';
proc sql;
select *
from work.bday
where bday = &usenum;
quit;

title 'c1) Use BETWEEN with macro var for comparison';
title2 "WHERE BDAY BETWEEN %sysfunc(inputn(&startchar,date9.)) and %sysfunc(inputn(&endchar,date9.)) (as numeric)";
proc sql;
select *
from work.bday
where bday between "&startchar"d and "&endchar"d;
quit;

title 'c2) Use other operators with macro var for comparison';
title2 "where bday GE %sysfunc(inputn(&startchar,date9.)) and bday LT %sysfunc(inputn(&endchar,date9.)) (as numeric)";
proc sql;
select *
from work.bday
where bday GE "&startchar"d and bday LT "&endchar"d;
quit;

title 'd) Try Simple EQ in WHERE';
title2 'This will appear to work';
proc sql;
select *
from work.bday
where charbday = "15Nov50";
quit;

title 'e) Try Character variables in the WHERE with diff condition';
title2 "where charbday GE &startchar and charbday LT &endchar (as character)";
title3 "This will NOT work";
proc sql;
select *
from work.bday
where charbday GE "&startchar" and charbday LT "&endchar";
quit;

title 'f) Try comparing numeric bday to character string';
title2 "where bday GE &startchar and charbday LT &endchar (as mixed type)";
title3 "This will NOT work";
proc sql;
select *
from work.bday
where bday GE "&startchar" and charbday LT "&endchar";
quit;

title;
[/pre]

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
  • 6 replies
  • 1606 views
  • 0 likes
  • 6 in conversation