topic Re: Using Proc SQL - Date Function in SAS Procedures

Using Proc SQL - Date Function

Pritish — Mon, 19 Apr 2010 20:15:53 GMT

Hi,

I have column in my dataset which contains date in the following format YYYYMMDD (20100419). Now using Proc SQL I want to know which day of the week and Day it is (For ex: Day of week : 1 && Day = Monday).

I am trying to use DATENAME in-built funtion available in SQL but it gives me error stating "Datename function could not be located".

My Query:
Proc SQL;
Select Datename(Weekday, Col_Name);
Quit;

Please help me out with this issue.

Thanks in advance.

Regards,
Pritish

Re: Using Proc SQL - Date Function

sbb — Mon, 19 Apr 2010 22:48:49 GMT

You will need to use a SAS PROC SQL compatible approach - one suggestion would be to use a SAS DATE (numeric) variable and combine it with an output format DOWNAME in your SELECT.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic/post:

proc sql select formatted value site:sas.com

Re: Using Proc SQL - Date Function

Patrick — Tue, 20 Apr 2010 03:09:25 GMT

Hi

There are SQL flavours and not all functions are available in all implementations.

For SAS SQL:

data have;
Col_Name='20100419';
run;

Proc SQL;
Select put(input(Col_Name,yymmdd8.),weekdate.)
from have;
Quit;

You have first to translate the (date) text string into a numeric SAS date (input) and then you can apply a format (put) on the result of how you want this SAS date printed.

You'll find more formats here:
http://support.sas.com/documentation/cdl/en/allprodslang/62304/HTML/default/syntaxByCategory-format.htm

And if none of these formats suits your need then you can also construct your own as a picture format (PROC FORMAT).

HTH
Patrick

Re: Using Proc SQL - Date Function

pepevo — Tue, 28 May 2019 15:02:14 GMT

I have a same issue to convert a numeric(8) to date as YYYY-MM-DDl.

my table has a column, load_dt_id numeric (8)

and in the table load_dt_id is 20161103

when I run procsql:

select input(put(load_dt_id, 10.),YYYYMM10.) as LOAD_DT from table_name;

and my result is still the same as 20161103 instead of 2016-11-03.

how do I convert it from numeric to date, YYYY-MM-DD or to date with time?

thank you.

Bach-Nga

Re: Using Proc SQL - Date Function

Tom — Tue, 28 May 2019 15:13:22 GMT

@pepevo wrote:

I have a same issue to convert a numeric(8) to date as YYYY-MM-DDl.

my table has a column, load_dt_id numeric (8)

and in the table load_dt_id is 20161103

when I run procsql:

select input(put(load_dt_id, 10.),YYYYMM10.) as LOAD_DT from table_name;

and my result is still the same as 20161103 instead of 2016-11-03.

how do I convert it from numeric to date, YYYY-MM-DD or to date with time?

thank you.

Bach-Nga

If you want to store hyphens in the value the you will need to create a character variable.

If you want your DATE values to display in that style the use the YYMMDDD10. format. The third D is for DASH. You can leave off the third D as the default for the YYMMDD format is to use a dash when the width is long enough to have room for it.

Note that there is no YYYYMM informat. There is a YYMMDD informat and a YYMM informat (which will set the date value to the first of the month).

So if you have numeric values in YY,YYM,MDD format then you can convert them to DATE values and attach a format to have them displayed as YYYY-MM-DD values using syntax like this in SQL.

 input(put(load_dt_id, 8.),YYMMDD8.) as LOAD_DT format=yymmdd10.

If you want to include a time component then you need to use DATETIME variable instead of DATE variable. Dates are stored as number of days. DATETIME is stored as number of seconds. You can use DATEPART() and TIMEPART() to extract date and time values from datetime values. You can use DHMS() function to construct datetime values from the components (D=Days, H=Hours, M=Minutes, S=Seconds). So if you have DATE and a TIME value you can construct a DATETIME value using:

dhms(date,0,0,time)

Re: Using Proc SQL - Date Function

pepevo — Tue, 28 May 2019 15:16:07 GMT

I did try that but didn't put "dd" in it.

select input(put(load_dt_id, 10.),YYYYMM10.) as LOAD_DT format=yymm10. from table_name;

Thank you for correcting my syntax so much.

v/r,

Bach_Nga