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
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.
i know in datastep
but i am trying to get output through proc sql
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;
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;
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
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.