topic Re: how to write sql code in SAS Programming

how to write sql code

shlomiohana — Sun, 29 May 2022 20:42:19 GMT

Hello,

I have the following table named CALLS:

The code to create the table:

data CALLS;
	input FIRST_NAME $ DAY_OF_MONTH :ddmmyy10. NUM_OF_CALLS;
	format DAY_OF_MONTH ddmmyy10.;
	datalines;
TONY 01/01/2021 75
STEVE 01/01/2021 61
NATASHA 01/01/2021 50
TONY 02/01/2021 64
STEVE 02/01/2021 80
NATASHA 02/01/2021 100
TONY 03/01/2021 33
STEVE 03/01/2021 99
NATASHA 03/01/2021 91
TONY 04/01/2021 70
STEVE 04/01/2021 71
NATASHA 04/01/2021 70
;
run;

I have 2 questions:

1. Who are the employees who made the same number of calls that day?

The output:

2. Add to the table CALLS a field that shows the number of calls the day before.

The output:

I need to answer 2 questions in a code, preferably in sql code.

Thanks.

Re: how to write sql code

PaigeMiller — Sun, 29 May 2022 18:39:12 GMT

Don't do this in SQL.

Question 1:

proc sort data=have;
    by num_of_calls;
run;
data want1;
    set have;
    prev_num_of_calls=lag(num_of_calls);
    prev_first_name=lag(first_name);
    if prev_num_of_calls=num_of_calls then 
        put 'Answer to Q1: ' first_name prev_first_name;
run;

Question 2:

proc summary data=have nway;
    class day_of_month;
    var num_of_calls;
    output out=_stats_ sum=sum_num_of_calls;
run;
data previous_day;
    set _stats_;
    day_of_month=day_of_month+1;
run;
data want;
    merge have previous_day(rename=(sum_num_of_calls=prev_day_sum_num_of_calls));
    by day_of_month;
run;

Re: how to write sql code

mkeintz — Sun, 29 May 2022 20:24:04 GMT

What if you have multple sets of tied number of calls on a given day? Say two people have 67 calls and two others have 59 calls.

Also, please show what your desired output should look like.

Re: how to write sql code

shlomiohana — Sun, 29 May 2022 20:45:07 GMT

I edited the post, now there is output.

Re: how to write sql code

mkeintz — Sun, 29 May 2022 22:43:40 GMT

You apparently want the output base on the names that have tied calls in the form of a report, not a dataset.

But what if you have multiple ties on the same day, but at different levels?

What if you have ties on different days?

Your simple posting of a pair of names that have the only tie in your sample data would not help the consumer of your report to know what they are looking at. They just know two names. They don't know at what level of the tie took place, and they don't know when it occurred.

Re: how to write sql code

Ksharp — Mon, 30 May 2022 12:15:53 GMT

data CALLS;
 input FIRST_NAME $ DAY_OF_MONTH :ddmmyy10. NUM_OF_CALLS;
 format DAY_OF_MONTH ddmmyy10.;
 datalines;
TONY 01/01/2021 75
STEVE 01/01/2021 61
NATASHA 01/01/2021 50
TONY 02/01/2021 64
STEVE 02/01/2021 80
NATASHA 02/01/2021 100
TONY 03/01/2021 33
STEVE 03/01/2021 99
NATASHA 03/01/2021 91
TONY 04/01/2021 70
STEVE 04/01/2021 71
NATASHA 04/01/2021 70
;
run;

proc sql;
create table want1 as
select *
 from CALLS
  group by DAY_OF_MONTH,NUM_OF_CALLS
   having count(*) ne 1;

create table want2 as
select a.*,b.NUM_OF_CALLS as NUM_OF_CALLS_before
 from CALLS as a left join CALLS as b
  on a.FIRST_NAME=b.FIRST_NAME and a.DAY_OF_MONTH > b.DAY_OF_MONTH 
   group by a.FIRST_NAME,a.DAY_OF_MONTH,a.NUM_OF_CALLS
    having a.DAY_OF_MONTH - b.DAY_OF_MONTH = min(a.DAY_OF_MONTH - b.DAY_OF_MONTH)
     order by 2,1;
quit;

Re: how to write sql code

Ksharp — Mon, 30 May 2022 12:16:45 GMT

data CALLS;
 input FIRST_NAME $ DAY_OF_MONTH :ddmmyy10. NUM_OF_CALLS;
 format DAY_OF_MONTH ddmmyy10.;
 datalines;
TONY 01/01/2021 75
STEVE 01/01/2021 61
NATASHA 01/01/2021 50
TONY 02/01/2021 64
STEVE 02/01/2021 80
NATASHA 02/01/2021 100
TONY 03/01/2021 33
STEVE 03/01/2021 99
NATASHA 03/01/2021 91
TONY 04/01/2021 70
STEVE 04/01/2021 71
NATASHA 04/01/2021 70
;
run;

proc sql;
create table want1 as
select *
 from CALLS
  group by DAY_OF_MONTH,NUM_OF_CALLS
   having count(*) ne 1;
quit;

data want2;
if _n_=1 then do;
 if 0 then set CALLS(rename=(NUM_OF_CALLS=NUM_OF_CALLS_before)) ;
 declare hash h();
 h.definekey('FIRST_NAME');
 h.definedata('NUM_OF_CALLS_before');
 h.definedone();
end;
set CALLS;
if h.find()=0 then h.replace(key:FIRST_NAME,data:NUM_OF_CALLS);
 else do;h.add(key:FIRST_NAME,data:NUM_OF_CALLS);NUM_OF_CALLS_before=.;end;
run;

Re: how to write sql code

shlomiohana — Mon, 30 May 2022 19:59:05 GMT

You are amazing, thank you so much for the types of solution