Help using Base SAS procedures

Count with PROC Means

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Count with PROC Means

[ Edited ]
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.


Accepted Solutions
Solution
‎11-02-2016 05:06 PM
Super User
Posts: 5,503

Re: Count with PROC Means

Posted in reply to JediApprentice

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. 

View solution in original post


All Replies
Solution
‎11-02-2016 05:06 PM
Super User
Posts: 5,503

Re: Count with PROC Means

Posted in reply to JediApprentice

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. 

Super User
Posts: 19,789

Re: Count with PROC Means

Posted in reply to JediApprentice

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;
Respected Advisor
Posts: 3,799

Re: Count with PROC Means

[ Edited ]
Posted in reply to JediApprentice
proc summary data=mydata nway;
   class list ndc discount;
   output out=counts(drop=_type_ rename=(_freq_=Count_of_NDC))
      idgroup(out(ndc)=ProductServiceID);
   run;

Capture.PNG 

Frequent Contributor
Posts: 123

Re: Count with PROC Means

Posted in reply to data_null__

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 521 views
  • 2 likes
  • 4 in conversation