Count and percentage of data

[pdhokriya]

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:

[PaigeMiller]

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

[pdhokriya]

Posted in my question box

[Kurt_Bremser]

PROC FREQ does counts and percentages.

[pdhokriya]

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.

[PaigeMiller]

---

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

[PeterClemmensen]

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

[pdhokriya]

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

[PeterClemmensen]

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

[pdhokriya]

SAS dataset

[PeterClemmensen]

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% 

[PeterClemmensen]

@pdhokriya did this solve your problem?

[pdhokriya]

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%

[PeterClemmensen · Accepted solution]

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% 

[pdhokriya]

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