07-20-2015 03:27 AM
i have the following issue:
i have created the table Hollyday_Tab containing one column (Hollyday). A list of hollydays, on which the workers are not supposed to work. here is a subset
subset Dataset One contains two columns: the dates on which the documents are available (in_date) and the deadline to be done with the documents (out_date). However, the working_days for each document start the next day after its in_date.
i wrote a data step to count the number of days on which the workers do the job. the employee receives documents on in_date and has until the out_date to be done with them. And nobody works on saturdays and sundays.
note: working_process = working_days.
data Two ;
if out_date >= in_date then do;
do i=(in_date + 1) to out_date;
if weekday(i) in (2,3,4,5,6) then working_days = working_days + 1;
working_process = working_days;
Here comes my issue: the woking_days still includes the hollydays, on which the employees are not supposed to work. and i wrote a proc sql to count the number of hollydays between in_date and out_date,
which works well, when testing it with some random real in_date and out_date.
PROC SQL NOPRINT;
SELECT count(*) INTO :find_days
WHERE in_date <= Hollyday AND out_date >= Hollyday;
how do i get the numeric value of the macrovariable find_days in my datastep?
how do i use the call execute within my data step to get the correct final result of working process? Which is working_process = working_days - find_days?
is there another possibility else than call execute to soleve the problem?
07-20-2015 04:23 AM
Your request is very confusing. If you want find_days in your original dataset then just mere it on:
create table THREE as
from TWO TWO
left join (select count(*) from from HOLLYDAY where IN_DATE <= HOLLYDAY and OUT_DATE >= HOLLYDAY) B
As for your question on call execute, I am afraid I do not follow at all what you are saying. Post test data in a datastep, and required output.
07-20-2015 04:46 AM
Thx for the answer, i'll try it.
obviously i cannot whrite the following proc sql statement within the data step:
weekday(i) in (2,3,4,5,6) and where i not in (select hollyday from Hollyday).
so i wanted to know, how i should write tthe sql statement in a call execute, which is in the data step. besause i saw that the call execute is a possibility to run another step within the data step.
07-20-2015 04:56 AM
Well, you can subquery in proc sql:
create table WANT as
(select TASK from XYZ where ABC=A.ABC) as TASK
Secondly, call execute does Not execute one datastep within another. What it does it takes the string - i.e. call execute('data xyz'), and puts that string into the compiler After the current datastep has finished. If you explain clearly (with test data and expected output) we might be able to provide some code.