Help using Base SAS procedures

Using Proc SQL - Date Function

Reply
Contributor
Posts: 65

Using Proc SQL - Date Function

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Using Proc SQL - Date Function

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
Respected Advisor
Posts: 3,989

Re: Using Proc SQL - Date Function

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
Ask a Question
Discussion stats
  • 2 replies
  • 9031 views
  • 0 likes
  • 3 in conversation