BookmarkSubscribeRSS Feed
sugita
Calcite | Level 5

Hi,

This is my code

%_eg_conditional_dropds(SASUSER.QUERY_FOR_HD_YTD);

PROC SQL;

CREATE TABLE SASUSER.QUERY_FOR_HD_YTD AS

SELECT sc.ser_id,

  sc.reg_created,

  YEAR(DATEPART(sc.reg_created)) AS YEAR,

  WG.WOG_NAME

FROM ISPRD.ITSM_SERVICECALLS AS SC LEFT JOIN ISPRD.ITSM_CODES AS CD ON CD.COD_OID = SC.SER_CLO_OID

  INNER JOIN ISPRD.ITSM_CODES_LOCALE AS CL ON (CL.CDL_COD_OID = CD.COD_OID AND CL.CDL_LNG_OID = 1033)

  INNER JOIN ISPRD.ITSM_PER_CUSTOM_FIELDS AS PCF ON (ITSM_SERVICECALLS.SLC_SER_PER_OID = PCF.PEC_PER_OID)

  INNER JOIN ISPRD.ITSM_WORKGROUPS AS WG ON (PCF.PEC_WOG1_OID = WG.WOG_OID) /* entered by person primary workgroup */

WHERE SC.REG_CREATED BETWEEN "&MonthStartDate" AND "&MonthEndDate"

AND CL.CDL_NAME NOT IN ("Duplicate","Withdrawn")

AND WG.WOG_NAME = "Service Desk";

/* Only include the Service Desk from November 2009 */

/*AND WG.WOG_NAME IN ("CBS Desktop","Health Desktop Support", "Humanities Desktop", "S&E Desktop", "VC Desktop", "Service Desk",

  "CBS-Helpdesk","Health Helpdesk","Humanities Helpdesk","S&E Helpdesk","VC Helpdesk","VC IT Support");

*/

QUIT;

whenever i tried to run it sas eg 4.1 gives me error: expession using equals (=) has components that are of different data types when eg try to analyse the line AND WG.WOG_NAME = "Service Desk"

i check on the database end and the data type for WOG_NAME is varchar2 (ORACLE)

how do i compare this WOG_NAME that has a value of "Service Desk"

8 REPLIES 8
Tom
Super User Tom
Super User

Looks like you are trying to use character strings to specify datetime constants.

WHERE SC.REG_CREATED BETWEEN "&MonthStartDate" AND "&MonthEndDate"

I believe that Oracle only has DATETIME variables, it does not have DATE or TIME variables so you should code this as something like:

WHERE SC.REG_CREATED BETWEEN "&MonthStartDate:00:00"dt AND "&MonthEndDate:00:00"dt


Where the macro variables have values in DATE9 format.  01JAN2010 for example.

sugita
Calcite | Level 5

Hi Tom,

Thanks for the reply. I tried to change it and it gives me ERROR: Invalid date/time/datetime constant "&MonthStartDate:00:00"dt. I want to ask you what is the dt stand for?

I also believe that the macro variable value is in  DATE9 format like January 01, 2006 12:00:00 AM

sugita
Calcite | Level 5

Hi Tom,

Sorry, I just double check the type of my macro variables and apparently its timestamp.

Tom
Super User Tom
Super User

SAS would treat "January 01, 2006 12:00:00 AM" the same as any other character string. 

The proper way to express that as a SAS datetime literal would be "01JAN2006:00:00"dt.

That is why it was invalid when you added the DT suffix.

The dt stands for DateTime.  SAS has a number of literals that are specified by quoted strings with letter suffixes.

'01JAN2006'd  would be a date literal.

'01:30't would be a time liternal.

'3031'x would be a hexadecimal representation of a the string '01'.

'0101'b would be the number 5 as a binary literal.

'Strange Variable Name'n would be a name literal.

sugita
Calcite | Level 5

Hi Tom,

I tried to compare

WHERESC.REG_CREATED BETWEEN '01NOV2007:00:00:00'dt AND '30NOV2007:23:59:59'dt

and it works. it produce the output. So it is obvious that the data type of SC.REG_CREATED and &MonthStartDate is not the same.

how do i cast &MonthStartDate to the same data type as SC.REG_CREATED so i can compare these two variable?

Thank you in advance.

PS. MonthStartDate is a variable that is entered by the end user and it has format of November 01, 2007 00:00:00 AM

%_eg_conditional_dropds(SASUSER.QUERY_FOR_HD_YTD);

PROC SQL;

CREATE TABLE SASUSER.QUERY_FOR_HD_YTD AS

SELECT sc.ser_id,

  sc.reg_created,

  YEAR(DATEPART(sc.reg_created)) AS YEAR,

  WG.WOG_NAME

FROM ISPRD.ITSM_SERVICECALLS AS SC LEFT JOIN ISPRD.ITSM_CODES AS CD ON CD.COD_OID = SC.SER_CLO_OID

  INNER JOIN ISPRD.ITSM_CODES_LOCALE AS CL ON (CL.CDL_COD_OID = CD.COD_OID AND CL.CDL_LNG_OID = 1033)

  INNER JOIN ISPRD.ITSM_PER_CUSTOM_FIELDS AS PCF ON (ITSM_SERVICECALLS.SLC_SER_PER_OID = PCF.PEC_PER_OID)

  INNER JOIN ISPRD.ITSM_WORKGROUPS AS WG ON (PCF.PEC_WOG1_OID = WG.WOG_OID) /* entered by person primary workgroup */

WHERE SC.REG_CREATED BETWEEN '01NOV2007:00:00:00'dt AND '30NOV2007:23:59:59'dt

AND CL.CDL_NAME NOT IN ("Duplicate","Withdrawn")

AND WG.WOG_NAME = "Service Desk";

/* Only include the Service Desk from November 2009 */

/*AND WG.WOG_NAME IN ("CBS Desktop","Health Desktop Support", "Humanities Desktop", "S&E Desktop", "VC Desktop", "Service Desk",

  "CBS-Helpdesk","Health Helpdesk","Humanities Helpdesk","S&E Helpdesk","VC Helpdesk","VC IT Support");

*/

QUIT;

Tom
Super User Tom
Super User

How does the end user enter it? 

If it is just a text field then teach them to enter it in a format that SAS can handle.

If you can't then you will need to parse the "timestamp" format that you are using into something that SAS can understand.

I do not know of any SAS informat that can read that text string with the month spelled out and the time like that.

It is not that hard to convert to DATE9. format by processing the string.

I will use DT1 and DT2 as the macro variable names as that is about 10,000 times easier to type than MonthStartDate.

%let dt1=November 01, 2007 12:00:00 AM;

%let dt1=%quote(&dt1);

%let dt1=%scan(&dt1,2)%substr(&dt1,1,3)%scan(&dt1,3);

%let dt2=November 30, 2007 12:00:00 AM;

%let dt2=%quote(&dt2);

%let dt2=%scan(&dt2,2)%substr(&dt2,1,3)%scan(&dt2,3);

%put dt1=&dt1 dt2=&dt2;

Then you can use them in your WHERE clause.  (Make sure to use double quote characters so the macro variable can expand.)

.... WHERE ... BETWEEN "&dt1:00:00"dt AND "&dt2:23:59:59"dt

sugita
Calcite | Level 5

Hi Tom,

I comparing my code with the code of another query which is working

The code of query that is working

%_eg_conditional_dropds(SASUSER.Query1_for_QUERY_FOR_HD_YTD);

PROC SQL;

   CREATE TABLE SASUSER.Query1_for_QUERY_FOR_HD_YTD AS

   SELECT QUERY_FOR_HD_YTD.WOG_NAME,

          /* COUNT_OF_SER_ID */

            (COUNT(QUERY_FOR_HD_YTD.SER_ID)) AS COUNT_OF_SER_ID

      FROM SASUSER.QUERY_FOR_HD_YTD AS QUERY_FOR_HD_YTD

      WHERE QUERY_FOR_HD_YTD.REG_CREATED BETWEEN "&MonthStartDate"dt AND "&MonthEndDate"dt

      GROUP BY QUERY_FOR_HD_YTD.WOG_NAME;

QUIT;

my code that is not working

%_eg_conditional_dropds(SASUSER.QUERY_FOR_HD_YTD);

PROC SQL;

CREATE TABLE SASUSER.QUERY_FOR_HD_YTD AS

SELECT sc.ser_id,

  sc.reg_created,

  YEAR(DATEPART(sc.reg_created)) AS YEAR,

  WG.WOG_NAME,

  sc.reg_created

FROM ISPRD.ITSM_SERVICECALLS AS SC LEFT JOIN ISPRD.ITSM_CODES AS CD ON CD.COD_OID = SC.SER_CLO_OID

  INNER JOIN ISPRD.ITSM_CODES_LOCALE AS CL ON (CL.CDL_COD_OID = CD.COD_OID AND CL.CDL_LNG_OID = 1033)

  INNER JOIN ISPRD.ITSM_PER_CUSTOM_FIELDS AS PCF ON (ITSM_SERVICECALLS.SLC_SER_PER_OID = PCF.PEC_PER_OID)

  INNER JOIN ISPRD.ITSM_WORKGROUPS AS WG ON (PCF.PEC_WOG1_OID = WG.WOG_OID) /* entered by person primary workgroup */

WHERE SC.REG_CREATED BETWEEN "&MonthStartDate"dt AND "&MonthEndDate"dt

AND CL.CDL_NAME NOT IN ("Duplicate","Withdrawn")

AND WG.WOG_NAME = "Service Desk";

/* Only include the Service Desk from November 2009 */

/*AND WG.WOG_NAME IN ("CBS Desktop","Health Desktop Support", "Humanities Desktop", "S&E Desktop", "VC Desktop", "Service Desk",

  "CBS-Helpdesk","Health Helpdesk","Humanities Helpdesk","S&E Helpdesk","VC Helpdesk","VC IT Support");

*/

QUIT;

now in the first code that is working you can clearly deduct that I can compare &MonthStartDate with QUERY_FOR_HD_YTD.REG_CREATED. QUERY_FOR_HD_YTD is a table in SAS that is resulted from importing a data in sas. it is clear that &MonthStartDate is in the same data type of REG_CREATED. Now my question is

Is there a way in SAS EG to cast my oracle variable into a compatible SAS Data type so i can use it and compare it with my SAS &MonthStartDate variable?

Thank you very much in advance

shivas
Pyrite | Level 9

Hi,

I dont know it would be useful for you or not...but just give a try...

Check these options in SAS documentation.SASDATEFMT and DBSASTYPE options.

Thanks,

Shiva

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