Desktop productivity for business analysts and programmers

Proc SQL and YEAR

Reply
Occasional Contributor
Posts: 13

Proc SQL and YEAR

I am having a problem with YEAR function in my Proc SQL. For some reason when I want variable (RentPer) which contains date and time, to only display Year portion of the variable my EG is not letting me do it?

Are these two procedure compatible.
Super Contributor
Super Contributor
Posts: 3,174

Re: Proc SQL and YEAR

Your post indicates that you have a DATETIME variable/value. The DATETIME format does not support displaying only the year portion of the DATETIME value. You must use either DATEPART to extract the date and display the year-information with a suitable SAS format or use the YEAR function and display the value.

Future posts/replies with your SAS code (preferably as part of a SAS log) would be helpful with accurate feedback. Check here (may want to bookmark as favorite) for forum content posting guidelines with certain characters: http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc.
Occasional Contributor
Posts: 13

Re: Proc SQL and YEAR

OK this is what I get as my log output when I run my Project in the EG.



1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Query Builder1';
4 %LET _CLIENTPROJECTPATH='C:\Documents and Settings\zahiroviczl\My Documents\SAS\First
4 ! Projcet-Admitions.egp';
5 %LET _CLIENTPROJECTNAME='First Projcet-Admitions.egp';
6
7 ODS _ALL_ CLOSE;
8 OPTIONS DEV=ACTIVEX;
NOTE: Procedures may not support all options or statements for all devices. For details, see the
documentation for each procedure.
9 GOPTIONS XPIXELS=0 YPIXELS=0;
10 FILENAME EGSR TEMP;
11 ODS tagsets.sasreport12(ID=EGSR) FILE=EGSR STYLE=Analysis
11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/SharedFiles/BIClientStyles/4.2/Analysis.css
11 ! ") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE: Writing TAGSETS.SASREPORT12(EGSR) Body file: EGSR
12
13 GOPTIONS ACCESSIBLE;
14 %_eg_conditional_dropds(WORK.QUERY_FOR_RST09_NAICS_0000);
NOTE: Table WORK.QUERY_FOR_RST09_NAICS_0000 has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


15
16 PROC SQL;
17 CREATE TABLE WORK.QUERY_FOR_RST09_NAICS_0000 AS
18 SELECT t1.LegalName,
19 t1.TradeName,
20 t1.NAICS,
21 t3.RtnTotalSales,
22 t3.RtnTaxCollectable,
23 t3.RtnDueDate,
24 /* Calculation */
25 (YEAR(t3.PeriodBegin)) FORMAT=YEAR4. AS Calculation
26 FROM WORK.QUERY_FOR_RST09_NAICS AS t1, HST.RST09_PAYMENTS AS t2, HST.RST09_RETURNS AS
26 ! t3
27 WHERE (t1.AccountKey = t2.AccountKey AND t1.AccountKey = t3.AccountKey) AND t1.NAICS
27 ! IN ('711111', '711112',
28 '711190', '711311', '711512');
NOTE: Invalid argument to function YEAR. Missing values may be generated.
NOTE: Table WORK.QUERY_FOR_RST09_NAICS_0000 created, with 24205 rows and 7 columns.

29 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 1:10.86
cpu time 11.87 seconds


30
31
2 The SAS System 15:07 Friday, March 12, 2010

32 GOPTIONS NOACCESSIBLE;
33
34
35
36
37 %LET _CLIENTTASKLABEL=;
38 %LET _CLIENTPROJECTPATH=;
39 %LET _CLIENTPROJECTNAME=;
40
41 ;*';*";*/;quit;run;
42 ODS _ALL_ CLOSE;
43
44
45 QUIT; RUN;
46
Super Contributor
Super Contributor
Posts: 3,174

Re: Proc SQL and YEAR

Just curious if you read my reply...the part about using the DATEPART function? Here's an over-simplified test scenario for your consideration.


1 data x;
2 BeginDate = dhms(today(),2,2,2);
3 format BeginDate datetime15.;
4 putlog _all_;
5 run;

BeginDate=12MAR10:02:02 _ERROR_=0 _N_=1
NOTE: The data set WORK.X has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds


6 proc sql noprint;
7 select year(BeginDate) FORMAT=YEAR4. AS Calculation
8 from x;
NOTE: Invalid argument to function YEAR. Missing values may be generated.
9 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


10
11 proc sql noprint;
12 select year(datepart(BeginDate)) FORMAT=YEAR4. AS Calculation
13 from x;
14 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds




Scott Barry
SBBWorks, Inc.
Occasional Contributor
Posts: 13

Re: Proc SQL and YEAR

Thanks Scott.
It worked perfectly.
Ask a Question
Discussion stats
  • 4 replies
  • 203 views
  • 0 likes
  • 2 in conversation