SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

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

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. Smiley Happy


Accepted Solutions
Solution
‎10-30-2012 09:00 AM
Super User
Posts: 5,500

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

Posted in reply to EinarRoed

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.);

View solution in original post


All Replies
PROC Star
Posts: 7,468

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

Posted in reply to EinarRoed

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;

Frequent Contributor
Posts: 90

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

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. Smiley Happy

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?

Frequent Contributor
Posts: 90

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

Posted in reply to EinarRoed

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.

Respected Advisor
Posts: 4,173

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

Posted in reply to EinarRoed

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;

Solution
‎10-30-2012 09:00 AM
Super User
Posts: 5,500

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

Posted in reply to EinarRoed

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.);

Frequent Contributor
Posts: 90

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

Posted in reply to Astounding

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

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

Super User
Posts: 5,500

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

Posted in reply to EinarRoed

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.

Super User
Posts: 19,780

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

Posted in reply to EinarRoed

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 422 views
  • 6 likes
  • 5 in conversation