I was trying to convert this T SQL query to SAS format
CREATE DATABASE ORG; SHOW DATABASES; USE ORG;
CREATE TABLE Worker ( WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY INT(15), JOINING_DATE DATETIME, DEPARTMENT CHAR(25) );
INSERT INTO Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT)
VALUES (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
(002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
(003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
(004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
(005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
(006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
(007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
(008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');
This I changed to
proc sql; CREATE TABLE Worker ( WORKER_ID num, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY num, JOINING_DATE char(19) , DEPARTMENT CHAR(25) ); INSERT INTO Worker VALUES (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR') VALUES (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin') VALUES (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR') VALUES (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin') VALUES (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin') VALUES (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account') VALUES (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account') VALUES (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin'); quit;
I used the following to convert the joining_dates to SAS dates as follows :
data worker2; set worker(Rename=(JOINING_DATE=JD)); format JOINING_DATE datetime23.; JOINING_DATE=input(jd, anydtdte23.); run;
But the date come as like this only
01JAN1960:05:29:34
Is there any way to convert them to proper SAS dates?
... View more
