topic Re: proc freq in SAS Procedures

proc freq

pmpradhan — Tue, 28 Aug 2018 17:36:12 GMT

My data is as follow:

office id	staff id
U790	452363
U797	452354
U797	452363
U790	452356
U790	452363
U790	452336
U797	452346
U797	452383
U100	452363

I want to perform count proc freq and get table as:

office id	count of staff	percentage of staff id for each office id
U100	1	11.11%
U790	4	44.44%
U797	4	44.44%

I did proc freq data=have; table staff_id; by office_id; run; I got a weird result.

thanks

Re: proc freq

Reeza — Tue, 28 Aug 2018 17:59:45 GMT

Your code looks correct. What is your 'weird result'?

@pmpradhan wrote:

My data is as follow:

office id staff id

U790 452363

U797 452354

U797 452363

U790 452356

U790 452363

U790 452336

U797 452346

U797 452383

U100 452363

I want to perform count proc freq and get table as:

office id count of staff percentage of staff id for each office id

U100 1 11.11%

U790 4 44.44%

U797 4
44.44%

I did proc freq data=have; table staff_id; by office_id; run; I got a weird result.

thanks

Re: proc freq

novinosrin — Tue, 28 Aug 2018 18:27:24 GMT

Hi @pmpradhan I'm afraid for your expected result you do not need by group

data have;
input (officeid	staffid) ($);
cards;
U790	452363
U797	452354
U797	452363
U790	452356
U790	452363
U790	452336
U797	452346
U797	452383
U100	452363
;
proc freq data=have;
tables officeid/out=want;
run;

Re: proc freq

pmpradhan — Tue, 28 Aug 2018 21:51:36 GMT

Thanks for the code. How does this handle the duplicates counts of staffid?

Re: proc freq

pmpradhan — Tue, 28 Aug 2018 21:56:36 GMT

the result that I got was something like:

U100 452363 1

U790 452363 2

U790 452356 1

U790 452336 1

and so forth

If proc sql allows me to create the percentage column that is fine too.

Re: proc freq

novinosrin — Tue, 28 Aug 2018 21:58:47 GMT

Can you please clarify the results you want for your sample?

Re: proc freq

pmpradhan — Tue, 28 Aug 2018 22:04:38 GMT

something along the lines of
proc sql;
create table x as
select distinct officeid,
count distinct staffid,
(calculated officeid/calculated staffid)*100 from have;
quit;

the code might not be accurate but something along those lines should work.

Re: proc freq

Reeza — Tue, 28 Aug 2018 22:31:45 GMT

It depends partly on how you define your percentages but I'm a fan of the double proc freq for that type of work.

SQL with distinct works as well.

I illustrate both options here:

https://github.com/statgeek/SAS-Tutorials/blob/master/count_distinct_by_group.sas

*Double PROC FREQ;
proc freq data=sashelp.cars noprint;
table origin * make / out=origin_make;
run;

proc freq data=origin_make noprint;
table origin / out= distinct_freq;
run;

title 'PROC FREQ';
proc print data=distinct_freq;
run;
title 'PROC SQL';
proc print data=distinct_sql;
run;

If you want the percentages in the output data set make sure to specify OUTPCT or OUTPERCENT (can't recall which is correct) to capture the percents.