Query in SAS to format the DATE field

sindhuvava · New User Joined: 05 Dec 2006 Posts: 17 Location: Chennai

can anyone help me in doin this.
In the table i have a field OCCUPANCY_DT and the date is in the format
2006-12-02.
But i want the query output for this in the format 20061202.
How to write the query in SAS.
i tried by the followin code but its not workin:
SELECT SUBSTR(C.OCCUPANCY_DT,1,4)||SUBSTR(C.OCCUPANCY_DT,6,2)||
SUBSTR(C.OCCUPANCY_DT,9,2) FROM TABLE NAME;
The query message i am getting is:
Argument '1' of scalar function 'SUBSTR' is invalid.

Alan Voss · Posted: Wed Dec 06, 2006 11:00 pm

I'm assuming that OCCUPANCY_DT is a DATE column in a relational database like DB2 or ORACLE. If that is true, then the query will return the column as a SAS date variable and you can just assign it a format like:
format OCCUPANCY_DT yymmddn8.;
If you what it to be returned as a character string, then you need to "cast" it to char and then work from it there, or, within the SAS code, bring it back as a date and then do:
OCCUPANCY_DT1 = put(OCCUPANCY_DT,yymmddn8.);