Dateformat - import data from Oracle to SAS-table 1

[karlomutschler]

hi everyone,
with the following script we create a SAS-table from an Oracle DB:

rsubmit;
proc sql;
connect to oracle(user="&uname" password="&pw" path="db_path" buffsize=200);
create table TABLENAME as
select * from connection to oracle
(
select tbl1.column1 as label1,
tbl2.column2 as label2,
tbl1.valid_from as label3,
sum(tbl1.column5) as label4
from oratable1 tbl1,
oratable2 tbl2
where whereclause1
.
and whereclause5
group by otbl1.column1, tbl2.column2, tbl1.valid_from
)
;
disconnect from oracle;
quit;
endrsubmit;

tbl1.valid_from is imported into the SAS-table as Example: 05DEC2003:00:00:00
However, we would like to store tbl1.valid_from in the format: 05122003 = ddmmyyyy

Any suggestions most welcome.

Kind regards
Karlo

meistertools@gmx.net

 

[klaz2002]

karlomutschler,

From your question I am not sure if you understand the way SAS stores date values.
SAS has two types of vars in 8.2 and down, char & Numeric. With this in mind SAS sets date values in numeric format as the amount of days from 1/1/1960. One can attach a SAS format on that number value and have the data display as the date in your requested format mm/dd/yyyy. The same applies to the datetime values. The value is the amount of seconds from 1/1/1960. Again with the proper format attached the data will display in date form.

The values that you get from oracle should be the datetime value. You could put a DATEPART() function on that variable (ex DATEPART(tbl1.valid_from) as your_new_var_name...). This will extract only the date value from that variable. Your new value will be the amount of days from 1.1.1960. If you would like to extract the date in text form you could use the put function around the datepart function (ex put(datepart(tbl1.valid_from), mmddyy10.) ).

I hope that this helps you.
Klaz2002

 

[karlomutschler]

Thank you Klaz2002,
for your valued reply.

After executing the script (similar to above) we have added
the following datastep:

data tablename_new;
set tablename_old(rename=(valid_from=old_valid_from));
drop old_valid_from;
valid_from=datepart(old_valid_from);
format valid_from ddmmyy.;
run;

With format columnname ddmmyy.;
the date is displayed with VIEWTABLE as Example: 05/12/03

However, we would like the date to be displayed as 05122003

Any suggestions most welcome
Kind regards
Karlo

meistertools@gmx.net

 

[klaz2002]

Once you have the SAS date value you could format that value in the following manner: MMDDYYn8. (ex. format date_var mmddyyn8.) This should display the date that you like.
Klaz2002

 

[karlomutschler]

Thank you Klaz,

this exactly what I needed.

Kind regards.


meistertools@gmx.net