topic Re: using the call execute in SAS Programming

using the call execute, proc sql, or macro variable

Landry — Mon, 20 Jul 2015 07:27:59 GMT

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

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.

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!!

Re: using the call execute

RW9 — Mon, 20 Jul 2015 08:23:49 GMT

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.

Re: using the call execute

Landry — Mon, 20 Jul 2015 08:46:40 GMT

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.

Re: using the call execute

RW9 — Mon, 20 Jul 2015 08:56:24 GMT

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.

Re: using the call execute

Landry — Mon, 20 Jul 2015 09:36:25 GMT

i have provided you with some test data.