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.
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.
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.
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;
proc summary data=mydata nway;
class list ndc discount;
output out=counts(drop=_type_ rename=(_freq_=Count_of_NDC))
idgroup(out(ndc)=ProductServiceID);
run;
@data_null__ @Reeza @Astounding Wow you guys are awesome!
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.
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.