Count with PROC Means

[JediApprentice]

data mydata;
  infile datalines;
  input list $ NDC discount;
datalines;
Nov 11 50
Dec 12 20
Nov 11 50
May 12 .
Mar 11 30
;
run;

My data looks like this

list NDC discount

Nov  11  50
Dec  12  20
Nov  11  50
May  12  .
Mar  11  30

And I want it to look like this:

list ProductServiceID NDC discount Count_of_NDC
Dec  12               12  20       1
Mar  11               11  30       1
Nov  11               11  50       2

That is, I want to create a count of NDC as well as create a new variable called ProductServiceID that is equal to NDC.

This Proc Sql will accomplish this:

proc sql;
  create table mydata2 as
  select list,
         NDC as ProductServiceID,
         NDC,
         discount,
         count(NDC) as Count_of_NDC
	from mydata
	where discount ne .
	group by list,NDC,discount;
quit;

However, I want to do it using proc means or data step or a combination of the two. Please help.

[Astounding · Accepted solution]

Counting is most easily accomplished by PROC FREQ:

proc freq data=have;

   tables list * NDC * discount / missing list out=counts (drop=percent rename=(count=Count_of_NDC));

run;

data want;

set counts;

ProductServiceID = NDC;

run;

If you don't want to actually print the table, PROC FREQ allows you to add the NOPRINT option.  You can choose whether to place it on the PROC statement or at the end of the TABLE statement options. 

[Reeza]

This should also get you there:

proc sort data=mydata;
	by list ndc discount;
run;

data want;
	set mydata;
	by list ndc discount;
	where discount ne .;

	if first.discount then
		count=1;
	else
		count+1;

	if last.discount then
		output;
run;

[data_null__]

proc summary data=mydata nway;
   class list ndc discount;
   output out=counts(drop=_type_ rename=(_freq_=Count_of_NDC))
      idgroup(out(ndc)=ProductServiceID);
   run;

 

[JediApprentice]

@data_null__ @Reeza @Astounding Wow you guys are awesome!