topic Re: How do I create a table using frequencies? in New SAS User

How do I create a table using frequencies?

AVUH777 — Tue, 11 Jul 2023 15:41:18 GMT

Hello,

So, my data is similar to this:

data employeesl;
input employee $ station $ gender $ race $ StationID;
datalines;
Smith west F B 124
Turner east M W 123
Porter north M W 125
William west F W 124
Lee south F B 126
Maine south M B 126
;
run;

I am looking to create a table similar to this:

Station	StationID	White Male	White Female	Black Male	Black Female
East	123	1	0	0	0
West	124	0	1	0	1
North	125	1	0	0	0
South	126	0	0	1	1
All Stations	999	2	1	1	2

Thank you!

Re: How do I create a table using frequencies?

Reeza — Tue, 11 Jul 2023 17:16:16 GMT

With table as is, this is fairly close:

proc tabulate data=employeesl;
class  station gender race stationID;
table (station*stationID all), race=''*gender=''*N='' / misstext='0';
run;

To have the gender/race headers all in one column and expanded, you can use formats and/or combine them into a single variable and then use the single variable instead of the race*gender in proc tabulate.

Is the table a SAS dataset or for a report, ie PDF?

data employeesl;
    input employee $ station $ gender $ race $ StationID;
    datalines;
Smith west F B 124
Turner east M W 123
Porter north M W 125
William west F W 124
Lee south F B 126
Maine south M B 126
;
run;

*formats to control display;

proc format;
    value $ race_fmt 'B'='Black' 'W'='White' other='Other';
    value $ gender_fmt 'F'='Female' 'M'='Male' other='Other';
run;

*generic tabulate;

proc tabulate data=employeesl;
    class station gender race stationID;
    table (station*stationID all), race=''*gender=''*N='' / misstext='0';
    format gender $gender_fmt. race $race_fmt.;
run;

*combining categorical variables of race/gender;

data employeesl;
    set employeesl;
    category=catx(" ", put(race, $race_fmt.), put(gender, $gender_fmt.));
run;

*tabulate with category combined;

proc tabulate data=employeesl out=long;
    class station stationID category;
    table (station*stationID all), category=''*N='' / misstext='0';
run;

proc sort data=long;
    by descending station stationId;
run;

*output in dataset;

proc transpose data=long out=wide (drop=_:);
    by descending station stationid;
    id category;
    var N;
run;

*fill in missing and add ALL label;

data wide;
    set wide;
    array _vars _numeric_;

    do over _vars;

        if _vars=. then
            _vars=0;
    end;

    if missing(station) then
        do;
            station='All';
            call missing(stationID);
        end;
run;

*display results;

proc print data=wide noobs;
run;

Re: How do I create a table using frequencies?

AVUH777 — Tue, 11 Jul 2023 17:15:09 GMT

Thank you so much! I tried to use proc tabulate originally, but something was wrong with my table statement. This fixed it! The table is for a report.

Re: How do I create a table using frequencies?

Ksharp — Wed, 12 Jul 2023 11:39:21 GMT

/*Just for having some fun.*/
data employeesl;
    input employee $ station $ gender $ race $ StationID;
    datalines;
Smith west F B 124
Turner east M W 123
Porter north M W 125
William west F W 124
Lee south F B 126
Maine south M B 126
;
run;
proc sql;
select station,StationID,
sum(gender='M' and race='W') as White_Male,
sum(gender='F' and race='W') as White_Female,
sum(gender='M' and race='B') as Black_Male,
sum(gender='F' and race='B') as Black_Female
 from employeesl
  group by station,StationID
union all
select 'All Stations',999,
sum(gender='M' and race='W') as White_Male,
sum(gender='F' and race='W') as White_Female,
sum(gender='M' and race='B') as Black_Male,
sum(gender='F' and race='B') as Black_Female
 from employeesl
;
quit;