topic Re: charactor to date format in SAS Programming

charactor to date format

BrahmanandaRao — Thu, 28 Nov 2019 14:19:33 GMT

proc sql;
create table Job_History(employee_id num , start_date varchar2(15),  end_date varchar2(15), job_id varchar2(15),  department_id num ); 
insert into Job_History values (102   '2001-01-13'   '2006-07-24'   'IT_PROG'                 60 ); 
insert into Job_History values (101   '1997-09-21'   '2001-10-27'   'AC_ACCOUNT'             110 );  
insert into Job_History values (101   '2001-10-28'   '2005-03-15'   'AC_MGR'                 110 );  
insert into Job_History values (201   '2004-02-17'   '2007-12-19'   'MK_REP'                  20 );  
insert into Job_History values (114   '2006-03-24'   '2007-12-31'   'ST_CLERK'                50 );  
insert into Job_History values (122   '2007-01-01'   '2007-12-31'   'ST_CLERK'                50 );  
insert into Job_History values (200   '1995-09-17'   '2001-06-17'   'AD_ASST'                 90 );  
insert into Job_History values (176   '2006-03-24'   '2006-12-31'   'SA_REP'                  80 );  
insert into Job_History values (176   '2007-01-01'   '2007-12-31'   'SA_MAN'                  80 );  
insert into Job_History values (200   '2002-07-01'   '2006-12-31'   'AC_ACCOUNT'              90 );  
quit;


/*Write a query in SQL to display job ID for those jobs that were done by two or more for more than 300 days.*/

proc sql;
SELECT job_id 
FROM job_history 
WHERE input (end_date mmddyy10. ,start_date,mmddyy10.	)	
GROUP BY job_id 
HAVING COUNT(*)>=2;
quit;

In the above table start_date and end_date in character datatype so in the query how to convert char to date format to find the difference between two dates

Re: charactor to date format

Kurt_Bremser — Thu, 28 Nov 2019 06:56:06 GMT

Maxim 33: Intelligent Data makes for Intelligent Programs.

Start by making your data intelligent, using proper types for date and identification columns:

data Job_History;
input employee_id :$3. start_date :yymmdd10. end_date :yymmdd10. job_id :$15. department_id :$3.;
format start_date end_date yymmddd10.;
datalines;
102 2001-01-13 2006-07-24 IT_PROG 60
101 1997-09-21 2001-10-27 AC_ACCOUNT 110
101 2001-10-28 2005-03-15 AC_MGR 110
201 2004-02-17 2007-12-19 MK_REP 20
114 2006-03-24 2007-12-31 ST_CLERK 50
122 2007-01-01 2007-12-31 ST_CLERK 50
200 1995-09-17 2001-06-17 AD_ASST 90
176 2006-03-24 2006-12-31 SA_REP 80
176 2007-01-01 2007-12-31 SA_MAN 80
200 2002-07-01 2006-12-31 AC_ACCOUNT 90
;

Then the code almost writes itself.

Re: charactor to date format

BrahmanandaRao — Thu, 28 Nov 2019 08:12:42 GMT

i know in datastep

but i am trying to get output through proc sql

Re: charactor to date format

Kurt_Bremser — Thu, 28 Nov 2019 08:43:14 GMT

Adapt your SQL to create intelligent data:

proc sql;
create table Job_History(employee_id char(3) , start_date date format=yymmddd10.,  end_date date format=yymmddd10., job_id char(15),  department_id char(3) ); 
insert into Job_History values ('102'   '13jan2001'd   '24jul2006'd   'IT_PROG'  '60' ); 
quit;

Re: charactor to date format

gamotte — Thu, 28 Nov 2019 11:54:17 GMT

Hello,

The correct syntax for the input function is

input(character_variable_or_string_litteral, informat)

where the informat tells how the string in first argument should be interpreted.

proc sql;
SELECT job_id
FROM job_history 
WHERE intck('day',input(start_date,yymmdd10.), input(end_date,yymmdd10.))>300
GROUP BY job_id 
HAVING COUNT(*)>=2;
quit;

Re: charactor to date format

Tom — Thu, 28 Nov 2019 14:28:40 GMT

You use formats to convert values into text and informats to convert text into values.

So if you want to convert a character string into a date VALUE (not a date FORMAT, a format in SAS is instructions on how to display data.) You can use the INPUT() function with an informat that knows how to convert that style of string into a date value.

input(start_date,yymmdd10.)

But why are you making your it so you need to do this? Why not define the data as a date to begin with and insert date values instead of strings?

insert into Job_History values (102   '13JAN2001'd   '24JUL2006'd   'IT_PROG'                 60 );

And why use the clunky SQL syntax to make the dataset to begin with? Just use a simple data step instead.

data Job_History;
  length employee_id 8 start_date 8 end_date 8 job_id $15 department_id 8 ;
  format start_date end_date date9.;
  informat start_date end_date yymmdd.;
  input employee_id -- department_id;
cards;
102  2001-01-13  2006-07-24 IT_PROG        60 
101  1997-09-21  2001-10-27 AC_ACCOUNT    110  
101  2001-10-28  2005-03-15 AC_MGR        110  
201  2004-02-17  2007-12-19 MK_REP         20  
114  2006-03-24  2007-12-31 ST_CLERK       50  
122  2007-01-01  2007-12-31 ST_CLERK       50  
200  1995-09-17  2001-06-17 AD_ASST        90  
176  2006-03-24  2006-12-31 SA_REP         80  
176  2007-01-01  2007-12-31 SA_MAN         80  
200  2002-07-01  2006-12-31 AC_ACCOUNT     90  
;