Help using Base SAS procedures

comparing varchar

Reply
Occasional Contributor
Posts: 15

comparing varchar

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"

Super User
Super User
Posts: 6,163

Re: comparing varchar

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.

Occasional Contributor
Posts: 15

Re: comparing varchar

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

Occasional Contributor
Posts: 15

Re: comparing varchar

Hi Tom,

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

Super User
Super User
Posts: 6,163

Re: comparing varchar

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.

Occasional Contributor
Posts: 15

Re: comparing varchar

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;

Super User
Super User
Posts: 6,163

Re: comparing varchar

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

Occasional Contributor
Posts: 15

Re: comparing varchar

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

Super Contributor
Posts: 349

Re: comparing varchar

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

Ask a Question
Discussion stats
  • 8 replies
  • 368 views
  • 0 likes
  • 3 in conversation