topic Importing TSQl query to SAS Proc sql in SAS Procedures

Importing TSQl query to SAS Proc sql

thesasuser — Sun, 01 Apr 2018 14:23:39 GMT

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?

Re: Importing TSQl query to SAS Proc sql

Tom — Sun, 01 Apr 2018 15:50:12 GMT

Is your question really about how to specify a datetime literal?

Note sure what a value like '14-02-20 09.00.00' is supposed to mean, but for a guess you might have meant to use.

'14FEB2020:09:00:00'dt

The reason your values look like they are from 1960 is that you applied a datetime format to a date value. Date values are stored as the number of days and datetime values are stored as the number of seconds. If you want to convert those strings to datetime values then use the ANYDTDTM. informat instead of the ANYDTDTE. informat. The former will create a datetime value while the later creates a date value. But since the variable name implies that you just want the date and don't care about the time of day part then perhaps you should leave the informat the same and chave the format used to display it to a date format like YYMMDD10. or DATE9. to the value instead and ignore the 9AM part of the original value.

In the long run it will probably be easier to write a data step to read the values instead of using SQL INSERT statements.

data worker;
  infile cards dsd dlm=',' truncover ;
  input WORKER_ID FIRST_NAME :$25. LAST_NAME :$25. SALARY JOINING_DATE :anydtdtm. DEPARTMENT :$25.;
  format  worker_id z3. joining_date datetime20.;
cards;
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'
;

Re: Importing TSQl query to SAS Proc sql

thesasuser — Sun, 01 Apr 2018 17:03:34 GMT

Thanks Tom for your solution.

It worked like a charm.

My mistake was in using ANYDTDTE. instead of ANYDTDTM.

Thanks once again.