topic Re: comparing varchar in SAS Procedures

comparing varchar

sugita — Thu, 03 May 2012 05:26:46 GMT

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"

Re: comparing varchar

Tom — Thu, 03 May 2012 05:34:54 GMT

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.

Re: comparing varchar

sugita — Thu, 03 May 2012 06:04:03 GMT

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

Re: comparing varchar

sugita — Thu, 03 May 2012 06:15:55 GMT

Hi Tom,

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

Re: comparing varchar

Tom — Thu, 03 May 2012 06:23:34 GMT

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.

Re: comparing varchar

sugita — Thu, 03 May 2012 06:44:52 GMT

Hi Tom,

I tried to compare

WHERE

SC.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;

Re: comparing varchar

Tom — Thu, 03 May 2012 07:25:14 GMT

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

Re: comparing varchar

sugita — Fri, 04 May 2012 03:58:36 GMT

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

Re: comparing varchar

shivas — Fri, 04 May 2012 04:38:43 GMT

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