topic Re: MTD % in SAS Programming

MTD %

vnreddy — Fri, 04 Aug 2023 12:47:48 GMT

Hi,

Could someone help me with MTD % how can I get this based on organisation A, B, C wise and status C for complete and P for pending.

data test;
input Date :Date9. Year Month $ Org $ Status $;
format Date date9.;
datalines;
07Dec2022 2022 Dec A C
14Jan2023 2023 Jan A C
02Feb2023 2023 Feb A C
02Feb2023 2023 Feb A P
03Feb2023 2023 Feb B C
21Feb2023 2023 Feb C C
24Feb2023 2023 Feb C C
27Feb2023 2023 Feb C P
;
run;

Expected output: From above data org A in Feb is having 1 complete and 1 pending so my % completion should be 50% each, similarly org C in Feb total 3 out of which 2 is completed and 1 is pending.

Thanks

Vennapusa

Re: MTD %

PaigeMiller — Fri, 04 Aug 2023 12:59:04 GMT

I am not sure what MTD % is. I also request you explain how the column PER_COMP is computed from the original data, as I'm not sure I understand what you want.

Re: MTD %

Quentin — Fri, 04 Aug 2023 13:00:25 GMT

Can you show the code you have tried? It looks like you could get the answer with PROC FREQ, or maybe a prettier table with PROC TABULATE.

Re: MTD %

vnreddy — Fri, 04 Aug 2023 13:01:32 GMT

Hi @PaigeMiller

I am trying to calculate the % of C & P on a monthly basis.

Re: MTD %

PaigeMiller — Fri, 04 Aug 2023 13:04:08 GMT

@vnreddy wrote:

Hi @PaigeMiller

I am trying to calculate the % of C & P on a monthly basis.

Please avoid speaking in abbreviations, as I also don't understand C&P in this context. Please provide actual information that I requested, specifically "I also request you explain how the column PER_COMP is computed from the original data". Please don't ignore this request.

Re: MTD %

vnreddy — Fri, 04 Aug 2023 13:20:59 GMT

@PaigeMiller Status column C stands for Complete and P for Pending.

I am trying the Per_comp which is the % breakdown based on Complete and Pending status by Org and Month basis. As you can see e.g., Org C in the month of Feb has 3 total rows out of which 2 is with (complete) C status and 1 is (Pending) P status. Number of (complete) C status/total rows in Feb based on Org C, Number of (pending) P status/total rows in Feb based on Org C which is 66.37% & 33.33%

Re: MTD %

Kurt_Bremser — Fri, 04 Aug 2023 13:32:35 GMT

data test;
input Date :Date9. Year Month $ Org $ Status $;
format Date date9.;
yymm = input(cats(month,year),monyy7.);
format yymm yymmn6.;
datalines;
07Dec2022 2022 Dec A C
14Jan2023 2023 Jan A C
02Feb2023 2023 Feb A C
02Feb2023 2023 Feb A P
03Feb2023 2023 Feb B C
21Feb2023 2023 Feb C C
24Feb2023 2023 Feb C C
27Feb2023 2023 Feb C P
;

data want;
set test;
by org yymm;
if first.yymm
then do;
  comp = 0;
  n = 1;
end;
else n + 1;
comp + (status = "C");
if last.yymm;
mtd = comp / n;
format mtd percent7.2;
keep org yymm mtd;
run;

Re: MTD %

vnreddy — Fri, 04 Aug 2023 13:53:11 GMT

Hi @Kurt_Bremser

As per your logic i am getting below results.

I want to see both (complete) C and (Pending) P status for e.g., from source data for Org A in Feb we have total 2 rows out of which 1 is complete and 1 is pending.

Re: MTD %

Kurt_Bremser — Fri, 04 Aug 2023 14:02:08 GMT

Create a second variable that evaluates the other condition (status="P").

Re: MTD %

Quentin — Fri, 04 Aug 2023 14:25:06 GMT

Assuming you want a report, I would play with PROC TABULATE. Something like:

proc tabulate data=test order=data missing;
  class Year Month Org Status ;
  tables Year*Month*Org*Status,pctn<Status>='Per_comp' ;
run ;

Returns:

Re: MTD %

vnreddy — Fri, 04 Aug 2023 14:30:11 GMT

Hi @Quentin apart from tabular is there any other way of getting it in list table.

I need to join this table to an another table.

Re: MTD %

Quentin — Fri, 04 Aug 2023 14:33:10 GMT

@vnreddy wrote:

Hi @Quentin apart from tabular is there any other way of getting it in list table.

I need to join this table to an another table.

Yes, TABULATE has an out= option that will write an output dataset.

proc tabulate data=test order=data missing out=want;
  class Year Month Org Status ;
  tables Year*Month*Org*Status,pctn<Status>='Per_comp' ;
run ;

proc print data=want ;
run ;