topic Re: Query with Computed Columns after changes from numeric format to Date format. in SAS Enterprise Guide

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

_Joe — Wed, 24 Aug 2011 11:11:15 GMT

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,

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

LinusH — Wed, 24 Aug 2011 13:37:14 GMT

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

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

_Joe — Wed, 24 Aug 2011 15:15:59 GMT

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

Name	Type	Long	Format	Format L	Header 6
Flight_date	Number	8	BEST12.0	F12.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:

Name	Type	Long	Format	Format L
fecha_vuelo	Date	4	DDMMYY10.0	F12.0

And the data is:

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

Thanks.

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

art297 — Wed, 24 Aug 2011 15:25:37 GMT

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

;

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

ScottBass — Mon, 05 Sep 2011 05:03:52 GMT

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

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

_Joe — Tue, 06 Sep 2011 12:45:19 GMT

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