topic Re: Converting column of numeric variables to date in New SAS User

Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 12:49:51 GMT

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

Re: Converting column of numeric variables to date

VDD — Thu, 13 Jun 2019 12:57:34 GMT

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

Re: Converting column of numeric variables to date

PeterClemmensen — Thu, 13 Jun 2019 13:00:19 GMT

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;

Re: Converting column of numeric variables to date

Kurt_Bremser — Thu, 13 Jun 2019 13:00:29 GMT

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

Re: Converting column of numeric variables to date

Tom — Thu, 13 Jun 2019 13:05:18 GMT

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.

Re: Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 14:36:29 GMT

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

Re: Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 14:37:34 GMT

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

Re: Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 14:38:25 GMT

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

Re: Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 14:39:17 GMT

TransactionDate is a numeric column

Re: Converting column of numeric variables to date

Tom — Thu, 13 Jun 2019 14:58:34 GMT

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.

Re: Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 15:30:06 GMT

`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

Re: Converting column of numeric variables to date

geds133 — Thu, 13 Jun 2019 15:31:03 GMT

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;

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

Re: Converting column of numeric variables to date

Kurt_Bremser — Thu, 13 Jun 2019 17:07:56 GMT

@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.

Re: Converting column of numeric variables to date

Kurt_Bremser — Thu, 13 Jun 2019 17:11:32 GMT

@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.

Re: Converting column of numeric variables to date

geds133 — Mon, 17 Jun 2019 09:28:21 GMT

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

Re: Converting column of numeric variables to date

Kurt_Bremser — Mon, 17 Jun 2019 09:43:21 GMT

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