topic Re: Case Statement with Multiple values in New SAS User

Case Statement with Multiple values

Jwaryah — Wed, 31 Mar 2021 02:01:56 GMT

proc sql;

select distinct  
			month(A.Month_End),
			A.Month_End,
			A.RTAM_ID,
			Year(A.Month_End),
			&fyear.,
			&fy_prev.,
			SUM(case when product=66 then value else 0 end) as MTRC_DOL_VAL1, /*cl_Promoters*/
          SUM(case when product=67 then value else 0 end) as MTRC_DOL_VAL2, /*cl_Detractors*/
          SUM(case when product=25 then value else 0 end) as MTRC_DOL_VAL3, /*cl_Responses*/
		sum((case when month(A.Month_End) = 10 and Year(A.Month_End) = &fy_prev. and product=25 then A.Value)
			sum(when month(A.Month_End) = 11 and month(A.Month_End) IN (10,11) and Year(A.Month_End) = &fy_prev. and product=25 then A.Value)
			SUM(when month(A.Month_End) = 12 and month(A.Month_End) IN (10,11,12) and Year(A.Month_End) = &fy_prev. and product=25 then A.Value)
			SUM(when month(A.Month_End) = 1 and month(A.Month_End) IN (10,11,12,1) and Year(A.Month_End) = &fy_prev. and product=25 then A.Value)

else 0 end) as MTR1
from 

  	(SELECT distinct 
			t1.date,
			input(t1.date, yymmdd10.) format date9. as Month_End,
		  t1.transit				as Transit,
          (t1.transit*1000) + t1.am as RTAM_ID,
		  2000						as MTRC_ID,
		  'NPS'						as MTRC_NM,
			. as Month_IN_YEAR,
		 	. as fiscal_mth_of_year,
			. as mth_num,
			t1.value,
			t1.product
	FROM  nps.nps_master_&descdt. as t1
	
	where    t1.product in (25,66,67)) as A 
	Group by 1,2,3

	order by A.Month_End
;
quit;

Hi, I am new to SAS and trying to solve the issue of running totals between multiple years.

For example,

if the Month 11 value is 2

and Month 12 value 2 I want to sum in the Month 12 value = 4

Any help would be appreciated.

Re: Case Statement with Multiple values

Tom — Wed, 31 Mar 2021 04:03:35 GMT

What does your input data look like? What is it that you want out?

Why are you writing SQL code instead of just normal SAS code?

Are you trying to generate a running sum?

data want;
  set have ;
  running_sum + value;
run;

Something else?

Re: Case Statement with Multiple values

Jwaryah — Wed, 31 Mar 2021 12:51:56 GMT

Thanks for the response. Please see the attached dataset.

As I said i am new to SAS that was the reason using SQL code

But i can try to use the SAS instead using SQL.

Re: Case Statement with Multiple values

Tom — Wed, 31 Mar 2021 13:32:40 GMT

So first step is to post example data in a way that other can use it to help understand the problem and make it easy for them to provide you code.

Looks like you have this data:

data nps_tech ;
  input Month_End :date. Transit RTAM_ID MTRC_ID MTRC_NM :$3.
        Month_IN_YEAR fiscal_mth_of_year mth_num 
        MTRC_DOL_VAL1 MTRC_DOL_VAL2 MTRC_DOL_VAL3
  ;
  format Month_End date9. ;
datalines4;
28FEB2021  1386  1386704 2000 NPS . . . 1 1 2
28FEB2021  1386  1386721 2000 NPS . . . 1 1 2
28FEB2021  1386  1386829 2000 NPS . . . 0 0 1
28FEB2021  7215  7215455 2000 NPS . . . 2 0 2
28FEB2021 10054 10054072 2000 NPS . . . 1 0 2
28FEB2021 13820 13820245 2000 NPS . . . 0 1 1
28FEB2021 13820 13820378 2000 NPS . . . 1 0 1
28FEB2021 14455 14455608 2000 NPS . . . 0 1 1
28FEB2021 14456 14456450 2000 NPS . . . 0 1 1
28FEB2021 17262 17262057 2000 NPS . . . 1 0 1
;;;;

You also need to provide more explanation of what these variable are, especially since none of them have any descriptive labels attached.

Now describe what is it that you are trying to generate from this data? First describe in words what you are trying to create. Then provide the results that could be produced from this input data, preferable in a similar simple SAS data step format.

Then you can show any code you tried to use and explain how it failed to produce the results you wanted. Did it run with errors? If so show the SAS log so the errors can be seen in context of the code run.

Re: Case Statement with Multiple values

ballardw — Wed, 31 Mar 2021 19:49:45 GMT

Candidate for some repeated redundant inefficient code:

when month(A.Month_End) = 11 and month(A.Month_End) IN (10,11)

you have multiple statements where you have equality then membership of that same value in a list. 11 is ALWAYS in (10 11) so the second part of all of those is not needed unless you really want to type unneeded stuff.

You don't show us a definition for your &fy_prev macro variable but this looks somewhat suspect:

SUM(when month(A.Month_End) = 1 and month(A.Month_End) IN (10,11,12,1) and Year(A.Month_End) = &fy_prev.

because the year value very likely to be different than for the Oct, Nov and Dec data