BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
Opal | Level 21

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

4 REPLIES 4
Astounding
Opal | Level 21

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

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__
Jade | Level 19
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 

JediApprentice
Pyrite | Level 9

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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