Converting column of numeric variables to date

[geds133]

I have a column called `TransactionDate` which contains numbers that represent dates in the format '20190101' and I wish to convert it to date format. The code I have used is as such:

INPUT(put(TransactionDate,8.),yymmdd8.) AS TransactionDate FORMAT ddmmyy10.

This has not worked and I am fairly new to SAS. Could someone help me out here please.

Thanks

[VDD]

data have;
  TransactionDate=20190101;
  sas_date=input(put(TransactionDate,yymmdd8.),yymmdd8.);
  format sas_date date9.;
run;

[geds133]

This simply returns a single date instead of turning the whole column into dates.

[PeterClemmensen]

Hi and welcome to the SAS Communities.

Jsut to be clear, TransactionDate is a character variable?

In that case do

data test;
    TransactionDate='20190101';
    numTransactionDate=input(TransactionDate, yymmdd8.);
    format numTransactionDate ddmmyy10.;
run;

[geds133]

TransactionDate is a numeric column

[Kurt_Bremser]

Please post your log (use the {i} button for this, as it keeps the original formatting of the log).

If your data is as you stated, your code works:

data have;
input transactiondate;
datalines;
20190101
;

proc sql;
select
INPUT(put(TransactionDate,8.),yymmdd8.) AS TransactionDate FORMAT ddmmyy10.
from have;
quit;

Result:

  Transaction
         Date
 ------------
   01/01/2019

[geds133]

I am looking to turn the column into date format not just a singular number.

[Kurt_Bremser]

---

@geds133 wrote:

I am looking to turn the column into date format not just a singular number.

---

Utterly wrong. It will convert ALL values of column transactiondate in the "from" dataset. That my example dataset contains only one observation is because I did not want to waste time unnecessarily and am a lazy person by nature.

I strongly suggest that you work through the free online Programming 1 course to get a grasp of the most basic principles of SAS programming, so you don't fall victim to misunderstandings as happened above.

[Tom]

Are you sure it is not already a date that just uses the YYMMDDN8.  to display it that way?  Or a character variable?

What does proc contents show for that variable?  Is it character or numeric?  Does it have a format attached?  Remember that SAS only had two data types, fixed length strings and floating point numbers.  Dates are stored as the number of days since 1960 and only appear like dates to humans when a date type format is used.

[geds133]

Is it a numeric variable column, not in date format.

[Tom]

Your example code should work as PART of a larger SELECT statement inside of PROC SQL.

Please show the code you actually ran. If you got ERRORS from SAS show the LOG.  If you got the wrong result explain how it was wrong.

[geds133]

`proc sql;
CREATE TABLE WORK.twentyeighteen AS
SELECT
a.*,
INPUT(put(TransactionDate,8.),yymmdd8.) AS TransactionDate format ddmmyy10.
FROM
database.td AS a
WHERE
TransactionDate > 20180101;
quit;`

proc sql;
CREATE TABLE WORK.trans AS
SELECT
SUM(CashValue + ChequeValue + TransferValue) as TotalDeposit,
MONTH(TransactionDate) AS month
FROM
WORK.twentyeighteen
WHERE
TransactionCode = 1 
GROUP BY
month
ORDER BY
month;
quit;

I don'y receive any errors but the result is only a single row instead of a list of deposits per month

[Kurt_Bremser]

---

@geds133 wrote:

`proc sql;
CREATE TABLE WORK.twentyeighteen AS
SELECT
a.*,
INPUT(put(TransactionDate,8.),yymmdd8.) AS TransactionDate format ddmmyy10.
FROM
database.td AS a
WHERE
TransactionDate > 20180101;
quit;`

proc sql;
CREATE TABLE WORK.trans AS
SELECT
SUM(CashValue + ChequeValue + TransferValue) as TotalDeposit,
MONTH(TransactionDate) AS month
FROM
WORK.twentyeighteen
WHERE
TransactionCode = 1 
GROUP BY
month
ORDER BY
month;
quit;

I don'y receive any errors but the result is only a single row instead of a list of deposits per month

---

Run a proc freq on column month in dataset twentyeighteen with the same where condition (transactioncode = 1); I suspect you only have one distinct value there.

[geds133]

The month column does not exist in twentyeighteen. I create this variable in the next instance of CREATE TABLE.

[Kurt_Bremser]

Then run the SQL without the group by, and then the proc freq over month.