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
PROC Star

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
PROC Star

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

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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