topic Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"? in SAS Data Management

Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

EinarRoed — Mon, 29 Oct 2012 14:08:24 GMT

I have a data set that includes a numeric variable called "price_date". It's in the datetime20-format, and appears as "10DEC2010:23:00:00".

I need to extract all the rows where price_date =< current_date. However, current_date is a character variable that appears as "30OCT12", and it must remain as it is.

My question is: What kind of expression can I use in the mapping of an Extract-transformation, to transform price_date from the numeric format "10DEC2010:23:00:00" into the character format "10DEC12"?

Thanks for your time.

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

art297 — Mon, 29 Oct 2012 14:15:50 GMT

Could you use something like?:

data have;

informat dt datetime18.;

informat ch $7.;

input dt ch;

cards;

10DEC2010:23:00:00 10DEC12

10DEC2010:23:00:00 12DEC12

10DEC2012:23:00:00 09DEC12

;

proc sql;

create table want as

select *

from have

where datepart(dt)<=input(ch,date7.);

;

quit;

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

EinarRoed — Mon, 29 Oct 2012 14:29:15 GMT

Thanks, though I was hoping for something I might use as an expression in an Extract-transformation. Should have specified that I'm working in DI Studio.

UPDATE: I tried using put(price_date, date7.), but then all price_date rows became blank/null in the output data set. If I use put(price_date, 10.0) I get the number value of the datetime (16076412), so I figure I need to turn it into the number value of the date only, then transform that date value number into date9. Or perhaps there's an easier way?

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

EinarRoed — Mon, 29 Oct 2012 21:37:40 GMT

I've been tinkering with this for a while, and can't seem to make it work. I'd still be very appreciative for any advice on how to turn a numeric value like "08DEC2010:23:00:00" (datetime20) into a character value like "08DEC10" (date7) using an Extract-transformation mapping expression in DI Studio.

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

Reeza — Tue, 30 Oct 2012 04:01:37 GMT

If they're character variables then they won't compare properly, ie you can say date<price_date and expect the comparison to work properly.

You should make them both numbers instead, or in the comparison use INPUT for the current_date.

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

Patrick — Tue, 30 Oct 2012 10:42:23 GMT

If you want this for row selection then this doesn't go to the expression field of a row but belongs into a where or having clause (or as a row selection if you're using the Splitter).

The where clause would look somewhat like: datepart(price_date)=input("&current_date",date9.)

Here some sample code (pretty much what Art posted already). The code generated by DI Studio should look something along this line (if using a SQL transformation).

%let current_date=%sysfunc(today(),date9.);

data have;
format Price_Date datetime21.;
do price_date=intnx('dtday',datetime(),-5) to intnx('dtday',datetime(),5) by 20000;
output;
end;
run;

proc sql;
create table want as
    select *
    from have
    where datepart(price_date)=input("&current_date",date9.)
;
quit;

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

Astounding — Tue, 30 Oct 2012 13:00:47 GMT

Be very wary of using two-digit years. SAS will select which century they belong to, and that can vary depending on how your options are set.

Back to your question, here's how to get the result using a four-digit year.

length newvar $ 9;

newvar = put(price_date, datetime20.);

That will give you a four-digit year, in date9 format such as 10DEC2010. If you must remove the century, do it at your own risk!

Good luck.

Actually tested the code this time, and it looks like datetime20 is too wide. It will give you two leading blanks, since only 18 characters are needed. Instead, try:

length newvar $9;

newvar = put(price_date, datetime18.);

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

EinarRoed — Tue, 30 Oct 2012 13:20:12 GMT

Thanks, that worked perfectly! "put(price_date, datetime20.)", ack, so simple it hurts.

UPDATE: I've realized that I indeed need to use numeric dates in order to perform comparisons. Thanks for the foresight and advice, everyone.

Re: Transforming numeric "10DEC2010:23:00:00" into char "10DEC12"?

Astounding — Tue, 30 Oct 2012 13:44:23 GMT

Also note the added comment, about switching to datetime18.

More important, note that some of the posters here were thinking ahead about how you would use the results. While I answered the question, the results may not be useful. When you compare character strings, "01" < "02" no matter what the month and years are. So "01DEC12" < "02JAN10" as far as SAS is concerned.

Good luck.