topic Re: proc sql in SAS Programming

proc sql

yankee241 — Wed, 07 Nov 2018 09:54:21 GMT

Hello Everyone,

I need help for a report I'm doing on SAS 9.4 , I have to sum the number of customer calls depending on a date, let's say 31/01/2018 :

I need in seperate columns : The month sum, the last month sum, the last trimester sum and the last 12 months sum .

I really dont know how to do it , here a screenshot of the data :

Thanks in advance for any tips, I put on attach how this need to look

Re: proc sql

PeterClemmensen — Tue, 06 Nov 2018 13:58:43 GMT

Welcome to the SAS forum 🙂

You will have much better luck in finding a usable code answer if you provide us with some data that represents your actual data alog with a description of your desired result.

Re: proc sql

yankee241 — Tue, 06 Nov 2018 14:04:49 GMT

The variable changing in the stored process will be the date, depending on the day they choose, I need to provide the first column with the blue title as the sum of the 1st of the month and the date (if it's 15/01/2018 , then it will be the 1st to the 15th) , the last month (december in this case) , the last 3 month and the last 12 month )

all depending on the site (region) and the job (PFS, Prod.... )

Re: proc sql

Kurt_Bremser — Tue, 06 Nov 2018 14:15:23 GMT

Start by giving us a picture of your data. Post some example data in a data step with datalines, so we can quickly and accurately recreate your dataset and play around with it.

Just to give you an idea how to solve it with a data step:

%let ref_date=%sysfunc(today());

data have;
input call_date :yymmdd10. no_of_calls;
datalines;
2018-01-01 1
2018-08-09 4
2018-10-03 2
2018-11-01 3
;
run;

data want (keep=sum_month sum_prev_month sum_trim sum_year);
set have end=eof;
retain
  sum_month 0
  sum_prev_month 0
  sum_trim 0
  sum_year 0
;
if intck('month',call_date,&ref_date.,'c') < 1 then sum_month + no_of_calls;
if 1 <= intck('month',call_date,&ref_date.,'c') < 2 then sum_prev_month + no_of_calls;
if intck('month',call_date,&ref_date.,'c') <= 4 then sum_trim + no_of_calls;
if intck('month',call_date,&ref_date.,'c') <= 12 then sum_year + no_of_calls;
if eof then output;
run;

Re: proc sql

yankee241 — Tue, 06 Nov 2018 14:31:16 GMT

Thank you for the tip, I will edit my first message once I have my data available; I'm not at work today.