| View previous topic :: View next topic |
| Author |
Message |
movam
Beginner
Joined: 25 Jun 2007
Posts: 3
Topics: 1
|
 Posted: Wed Sep 12, 2007 12:34 am Post subject: SAS on mainframe |
 |
|
Hi ,
I am trying to get begin date of the month using SAS.
I am working on SAS 9.1 on z/OS V01R07M00 platform.
| Code: |
PROC SQL;
CONNECT TO DB2 AS TAB (SSID=DB2D SERVER=ABCD);
%PUT &SQLXMSG;
CREATE TABLE DT AS
SELECT BEGN_DATE
FROM CONNECTION TO TAB (
select
CHAR(DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||
'-'||
RTRIM(CHAR(MONTH(CURRENT DATE)))||'-01') , ISO)
FROM SYSIBM.SYSDUMMY1
)
;
%PUT &SQLXMSG;
DISCONNECT FROM TAB;
QUIT;
PROC PRINT DATA=DT;
DATA _NULL_;
SET DT;
FILE OUTFILE1 NOTITLES;
PUT
@001 BEGN_DATE $10.
;
|
I am getting the following error:
| Code: |
DB2 SQL Error, sqlca->sqlcode=-7
SELECT CHAR(DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))|| '-'|| RTRIM(CHAR(MONTH(CURRENT DATE)))||'-01') , ISO) FROM SYSIBM.SYSDUMMY1
ERROR: DB2 prepare error: DSNT408I SQLCODE = -007, SQLSTATE = 42601, SYNTAX ERROR OR ACCESS RULE VIOLATION FROM DB2 UDB for
AIX, Linux, HP-UX, Sun, and Windows TOKENS | YEAR(CURRENT DATE))).
|
All your help will be greatly appreciated.
Thanks and Regards,
movam |
|
| Back to top |
|
 |
advoss
Beginner
Joined: 23 Aug 2005
Posts: 26
Topics: 0
|
| Posted: Wed Sep 12, 2007 7:54 am Post subject: |
|
|
Why not just do: | Code: |
date _null_;
begn_date = intnx("MONTH",today(),0,"B"); /* get first day of current month */
FILE OUTFILE1 NOTITLES;
PUT @001 BEGN_DATE yymmdd10.;
run; |
_________________
Alan Voss |
|
| Back to top |
|
|
movam
Beginner
Joined: 25 Jun 2007
Posts: 3
Topics: 1
|
| Posted: Wed Sep 12, 2007 8:47 am Post subject: |
|
|
Alan,
I appreciate your quick response on this. Yes, it works perfect for the Begin of month.
Can we try the same for end of the month.
Thanks and Regards,
Movam |
|
| Back to top |
|
|
vivek1983
Intermediate
Joined: 20 Apr 2006
Posts: 222
Topics: 24
|
| Posted: Wed Sep 12, 2007 9:06 am Post subject: |
|
|
movam,
Use the INTNX function to advance a SAS Date to the first of the month following
the month you want the last day of, then subtract 1 from the calculated date.
Remember that SAS dates are integer values, so the last day of a given month is
just 1 less than the first of the next month.
Vivek G
_________________
Vivek G
--------------------------------------
A dream is just a dream. A goal is a dream with a plan and a deadline. (Harvey Mackay) |
|
| Back to top |
|
|
movam
Beginner
Joined: 25 Jun 2007
Posts: 3
Topics: 1
|
| Posted: Wed Sep 12, 2007 9:40 am Post subject: |
|
|
Thanks guys,
I did the following for Begin of month and End of month,
Can you please clarify on the LASTDAY.
| Code: |
DATA _NULL_;
BEGN_DATE = INTNX("MONTH",TODAY(),0,"B");
[b]LASTDAY=INTNX("MONTH",TODAY(),0,"E") ;[/b]
FILE OUTFILE1 NOTITLES;
PUT @001 BEGN_DATE YYMMDD10.
@011 LASTDAY YYMMDD10.;
RUN;
|
I get | Code: |
2007-09-01 2007-09-30 |
|
|
| Back to top |
|
|
advoss
Beginner
Joined: 23 Aug 2005
Posts: 26
Topics: 0
|
| Posted: Wed Sep 12, 2007 11:31 am Post subject: |
|
|
Vivek's answer is correct, but your solution is probably more obvious from a maintenance standpoint. If you had the misfortune of learning SAS prior to about version 8, Vivek's approach was the way that you had to get the end of month.
_________________
Alan Voss |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
