BookmarkSubscribeRSS Feed
Pritish
Quartz | Level 8
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
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Patrick
Opal | Level 21
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....

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

HTH
Patrick
pepevo
Calcite | Level 5

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

 

Tom
Super User Tom
Super User

@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)

 

pepevo
Calcite | Level 5

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

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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
  • 5 replies
  • 18425 views
  • 1 like
  • 5 in conversation