MTD %

[vnreddy]

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

[PaigeMiller]

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.

[vnreddy]

Hi @PaigeMiller 

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

[PaigeMiller]

---

@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.

[vnreddy]

@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%

[Quentin]

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.

[Kurt_Bremser]

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;

[vnreddy]

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.

[Kurt_Bremser]

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

[Quentin]

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:

[vnreddy]

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.

[Quentin · Accepted solution]

---

@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 ;