topic Re: Count and percentage of data in SAS Programming

Count and percentage of data

pdhokriya — Sat, 28 Nov 2020 14:14:51 GMT

Hi Reader,

Can you please let me know how can I get attcahed output data.

Here is input data :

data abc;
input cd $ type $;
cards;
a new
a new
c new
a old
;

run;

requirement:

Re: Count and percentage of data

PaigeMiller — Sat, 28 Nov 2020 12:38:35 GMT

Many of us will not download Microsoft Office files as they are security threats. Please show the desired output in your message.

Re: Count and percentage of data

Kurt_Bremser — Sat, 28 Nov 2020 13:52:33 GMT

PROC FREQ does counts and percentages.

Re: Count and percentage of data

pdhokriya — Sat, 28 Nov 2020 14:15:25 GMT

Posted in my question box

Re: Count and percentage of data

pdhokriya — Sat, 28 Nov 2020 14:53:21 GMT

True, but considering future data this wont work.
As I wont to get values where records not present like "b", count would be for "a" and "c" records only.

Re: Count and percentage of data

PaigeMiller — Sat, 28 Nov 2020 15:04:57 GMT

@pdhokriya wrote:

True, but considering future data this wont work.
As I wont to get values where records not present like "b", count would be for "a" and "c" records only.

This is a case where additional requirements are mentioned after the initial question doesn't mention them. Can you please describe the entire requirements of the problem in one message?

In SAS, several procedures (TABULATE and REPORT and possibly others) allow a PRELOADFMT option which can produce a row of zeros if there are no values for a certain level of a variable.

Re: Count and percentage of data

PeterClemmensen — Sat, 28 Nov 2020 15:44:34 GMT

Are you only interested in the 'New' type or both new and old?

Re: Count and percentage of data

pdhokriya — Mon, 30 Nov 2020 09:18:52 GMT

actually both, but its fine if you share for one.

Re: Count and percentage of data

PeterClemmensen — Mon, 30 Nov 2020 09:36:25 GMT

Ok. Just to be clear, do you want this in a SAS data set or in a report of some sort?

Re: Count and percentage of data

pdhokriya — Mon, 30 Nov 2020 10:33:50 GMT

SAS dataset

Re: Count and percentage of data

PeterClemmensen — Mon, 30 Nov 2020 10:48:35 GMT

Ok. Here is one way. Let me know if it works for you

data abc;
input cd $ type $;
cards;
a new
a new
c new
a old
;

proc format;
   value $ fmt 'a' = 'a'
               'b' = 'b'
               'c' = 'c'
   ;
run;

proc summary data = abc nway completetypes;
   class type cd / preloadfmt;
   format cd $fmt.;
   output out = temp(drop = _TYPE_ rename = _FREQ_ = n);
run;

data want(drop = c);
   c = .;
   do until (last.type);
      set temp;
      by type;
      c + n;
   end;
   do until (last.type);
      set temp;
      by type;
      p = divide(n, c);
      output;
   end;
   format p percent8.2;
run;

Result:

type cd n  p 
new  a  2  66.67% 
new  b  0  0.00% 
new  c  1  33.33% 
old  a  1  100.0% 
old  b  0  0.00% 
old  c  0  0.00%

Re: Count and percentage of data

PeterClemmensen — Mon, 30 Nov 2020 11:44:03 GMT

@pdhokriya did this solve your problem?

Re: Count and percentage of data

pdhokriya — Mon, 30 Nov 2020 12:25:51 GMT

Yes, just a format issue i am getting it. could you please share how to i transpose these.

cd new_C new_p old_c old_p
a 2 66.67% 1 1 100.00%
b 0 0.00% 0 0 0.00%
c 1 33.33% 0 0 0.00%

Re: Count and percentage of data

PeterClemmensen — Mon, 30 Nov 2020 13:13:36 GMT

proc sort data = want;
   by cd;
run;

data want (drop = n p);
   do until (last.cd);
      set want;
      by cd;
      if type = 'new' then do;
         new_n = n;
         new_p = p;
      end;
      else do;
         old_n = n;
         old_p = p;
       end;
   end;
   format new_p old_p percent8.2;
run;

Result:

cd new_n new_p   old_n  old_p 
a  2     66.67%  1      100.0% 
b  0     0.00%   0      0.00% 
c  1     33.33%  0      0.00%

Re: Count and percentage of data

pdhokriya — Mon, 30 Nov 2020 15:36:36 GMT

@PeterClemmensen thank you so much for your quck reply. 🙂