using the call execute, proc sql, or macro variable

Reply
Occasional Contributor
Posts: 6

using the call execute, proc sql, or macro variable

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.200620.11.2006
01.12.200601.12.2006
05.12.200605.12.2006
11.12.200611.12.2006
19.12.200619.12.2006
21.12.200621.12.2006
08.02.200708.02.2007
08.02.200708.02.2007
12.02.200712.02.2007
26.02.200726.02.2007
14.03.200714.03.2007
17.04.200717.04.2007
17.04.200717.04.2007
07.06.200707.06.2007
29.06.200729.06.2007
30.07.200730.07.2007
04.09.200704.09.2007
25.09.200725.09.2007
09.11.200709.11.2007
12.07.200616.08.2006
29.03.200629.03.2006
07.09.200607.09.2006
29.08.200729.08.2007
19.01.200819.01.2008
25.07.200628.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!!

Super User
Super User
Posts: 7,942

Re: using the call execute

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.

Occasional Contributor
Posts: 6

Re: using the call execute

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.

Super User
Super User
Posts: 7,942

Re: using the call execute

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.

Occasional Contributor
Posts: 6

Re: using the call execute

i have provided you with some test data.

Ask a Question
Discussion stats
  • 4 replies
  • 312 views
  • 0 likes
  • 2 in conversation