Desktop productivity for business analysts and programmers

Query with Computed Columns after changes from numeric format to Date format.

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Query with Computed Columns after changes from numeric format to Date format.

I'm working with SAS Enterprise Guide I have a data with dates entered in NUMERIC format. I will like to change in into the DATE format (‘DD/MMM/YYYY’).

To do this (Querie Builder > Computed Column); but i don’t know how to do this. Can someone help me?

After that, I have to join data (LEFT JOIN) from 2 tables and realise the query by the date columns

Thanks,


Accepted Solutions
Solution
‎09-05-2011 01:03 AM
Super Contributor
Posts: 374

Re: Query with Computed Columns after changes from numeric format to Date format.

A date in SAS is just a number, expressed as the number of days since 01JAN1960.  It usually has an associated format for display purposes, so we humans can deduce that 18785 = 20110607.

So, it's hard for me to understand what you mean by "convert from number to date".  By "date" do you mean the numeric representation of a date, i.e. the correct number representing a given date?  Or rather the character representation of a SAS date variable, which is just a number?

The input function can be used to convert text input into either numeric or text output.  The put function can be used to convert either numeric or text input into text output.

To convert the below text to their equivalent dates (untested, see the doc if I've got the formats slightly wrong):

"20110607" ==> input("20110607",yymmdd8.); == 18785

"07/05/2011" ==> input("07/05/2011",mmddyy10.); == 18813  (I assume "07/05/2011" is 05JUL2011).

To convert the above dates to their character representation (you seem to prefer the ddmmyy10. format for your dates):

put(18785,ddmmyy10.) ==> 06/07/2011

put(18813,ddmmyy10.) ==> 07/05/2011

Putting it all together:

"20110607" ==> put(input("20110607,yymmdd8.),ddmmyy10.);

"07/05/2011" ==> put(input("07/05/2011",mmddyy10.).ddmmyy10.);

I recommend you figure out how to import your CSV file with your date columns as text, then using the input function to conver the text to date (numeric) format based on the format of your date text strings in your CSV file.  Then, either associate your date variables with your desired date format, or derive another variable containing the text representation of your dates.

Hope this helps,

Scott

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,198

Query with Computed Columns after changes from numeric format to Date format.

With format, do you mean SAS format or data type? (There is no SAS format named NUMERIC).

Please give an example of how your dates are stored. This goes for the date column in the tables you wish to join.

Basically, SAS dates are stored as integers. SAS formats are used to make them dynamically take different views.

So, if your dates are not stored as SAS date integers, you need convert them using the input function.

Then use an appropriate format for the display.

/Linus

Data never sleeps
New Contributor
Posts: 3

Re: Query with Computed Columns after changes from numeric format to Date format.


First step import a CSV file to a SAS table, then the format is:

NameTypeLongFormatFormat LHeader 6
Flight_dateNumber8BEST12.0F12.0

And the data is:

Flight_date
20110607
20110504

I convert them using the input function:  IMPW.flight_date 2=input(IMPW.flight_date,yyyymmdd.10)

But it doesn’t work!!

Question 1: I have to create the column IMPW.flight_date2 before to create the input function. But, where do you create the new column?

Question 2:  Do you know the input function to convert from number to date (ddmmyyyy)?     

The 2nd table has the next data format:

NameTypeLongFormatFormat L
fecha_vueloDate4DDMMYY10.0F12.0

And the data is:

fecha_vuelo
07/05/2011
09/06/2011
11/06/2011


Thanks.

Esteemed Advisor
Posts: 7,296

Re: Query with Computed Columns after changes from numeric format to Date format.

I think that you are looking for something like the following.  Note, in the code, that you can either create a new variable or simply replace the original, as both are numeric variables:

data have;

  input flight_date;

  flight_date2=input(put(flight_date,8.),yymmdd8.);

  flight_date=input(put(flight_date,8.),yymmdd8.);

  cards;

20110607

;

Solution
‎09-05-2011 01:03 AM
Super Contributor
Posts: 374

Re: Query with Computed Columns after changes from numeric format to Date format.

A date in SAS is just a number, expressed as the number of days since 01JAN1960.  It usually has an associated format for display purposes, so we humans can deduce that 18785 = 20110607.

So, it's hard for me to understand what you mean by "convert from number to date".  By "date" do you mean the numeric representation of a date, i.e. the correct number representing a given date?  Or rather the character representation of a SAS date variable, which is just a number?

The input function can be used to convert text input into either numeric or text output.  The put function can be used to convert either numeric or text input into text output.

To convert the below text to their equivalent dates (untested, see the doc if I've got the formats slightly wrong):

"20110607" ==> input("20110607",yymmdd8.); == 18785

"07/05/2011" ==> input("07/05/2011",mmddyy10.); == 18813  (I assume "07/05/2011" is 05JUL2011).

To convert the above dates to their character representation (you seem to prefer the ddmmyy10. format for your dates):

put(18785,ddmmyy10.) ==> 06/07/2011

put(18813,ddmmyy10.) ==> 07/05/2011

Putting it all together:

"20110607" ==> put(input("20110607,yymmdd8.),ddmmyy10.);

"07/05/2011" ==> put(input("07/05/2011",mmddyy10.).ddmmyy10.);

I recommend you figure out how to import your CSV file with your date columns as text, then using the input function to conver the text to date (numeric) format based on the format of your date text strings in your CSV file.  Then, either associate your date variables with your desired date format, or derive another variable containing the text representation of your dates.

Hope this helps,

Scott

New Contributor
Posts: 3

Re: Query with Computed Columns after changes from numeric format to Date format.

Thank you very much.

Finally, the solution for the first table was:

"20110607" ==> put(input("20110607,yymmdd8.),ddmmyy10.);

For the second table:

"07/05/2011" ==> put(input("07/05/2011",mmddyy10.).ddmmyy10.);

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 2030 views
  • 3 likes
  • 4 in conversation