BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
vnreddy
Quartz | Level 8

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.

vnreddy_0-1691153266922.png

 

Thanks

Vennapusa

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

View solution in original post

11 REPLIES 11
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
vnreddy
Quartz | Level 8

Hi @PaigeMiller 

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

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
vnreddy
Quartz | Level 8

@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
Super User

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.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Kurt_Bremser
Super User
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
Quartz | Level 8

Hi @Kurt_Bremser 

As per your logic i am getting below results.

vnreddy_1-1691157150661.png

 

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.

Quentin
Super User

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:

Tabulate.PNG

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
vnreddy
Quartz | Level 8

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
Super User

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

 

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1793 views
  • 4 likes
  • 4 in conversation