hi everyone,
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
Hollyday
01JAN2008
21MAR2008
23MAR2008
24MAR2008
01MAY2008
01MAY2008
11MAY2008
12MAY2008
03OCT2008
24DEC2008
25DEC2008
26DEC2008
31DEC2008
01JAN2009
10APR2009
12APR2009
13APR2009
01MAY2009
21MAY2009
31MAY2009
01JUN2009
...
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.
In_date Out_date
14.11.2006 | 14.11.2006 |
20.11.2006 | 20.11.2006 |
01.12.2006 | 01.12.2006 |
05.12.2006 | 05.12.2006 |
11.12.2006 | 11.12.2006 |
19.12.2006 | 19.12.2006 |
21.12.2006 | 21.12.2006 |
08.02.2007 | 08.02.2007 |
08.02.2007 | 08.02.2007 |
12.02.2007 | 12.02.2007 |
26.02.2007 | 26.02.2007 |
14.03.2007 | 14.03.2007 |
17.04.2007 | 17.04.2007 |
17.04.2007 | 17.04.2007 |
07.06.2007 | 07.06.2007 |
29.06.2007 | 29.06.2007 |
30.07.2007 | 30.07.2007 |
04.09.2007 | 04.09.2007 |
25.09.2007 | 25.09.2007 |
09.11.2007 | 09.11.2007 |
12.07.2006 | 16.08.2006 |
29.03.2006 | 29.03.2006 |
07.09.2006 | 07.09.2006 |
29.08.2007 | 29.08.2007 |
19.01.2008 | 19.01.2008 |
25.07.2006 | 28.07.2010 |
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.
a
data Two ;
set One;
working_days=0;
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;
end;
working_process = working_days;
end;
run;
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
FROM Hollyday_tab
WHERE in_date <= Hollyday AND out_date >= Hollyday;
QUIT;
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?
Thx!!
Your request is very confusing. If you want find_days in your original dataset then just mere it on:
proc sql;
create table THREE as
select TWO.*,
B.FIND_DAYS
from TWO TWO
left join (select count(*) from from HOLLYDAY where IN_DATE <= HOLLYDAY and OUT_DATE >= HOLLYDAY) B
on 1=1;
quit;
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.
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.
Well, you can subquery in proc sql:
proc sql;
create table WANT as
select ABC.ABC,
(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.
i have provided you with some test data.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.