topic Re: Proc SQL and YEAR in SAS Enterprise Guide

Proc SQL and YEAR

BigZ — Fri, 12 Mar 2010 20:19:26 GMT

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.

Re: Proc SQL and YEAR

sbb — Fri, 12 Mar 2010 20:29:17 GMT

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.

Re: Proc SQL and YEAR

BigZ — Fri, 12 Mar 2010 20:59:31 GMT

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

Re: Proc SQL and YEAR

sbb — Fri, 12 Mar 2010 22:01:47 GMT

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.

Re: Proc SQL and YEAR

BigZ — Mon, 15 Mar 2010 17:20:42 GMT

Thanks Scott.
It worked perfectly.