Summarizing data

[Reeza]

proc freq data=sashelp.class;

table sex*age/out=sample;

run;

proc transpose data=sample out=sample2 prefix=age;

by sex;

id age;

var count;

run;

[SAS_new]

Tried it multiple different ways and it does not appear to work. (including the count variable gave me the summarized data- thank you!)  But I am still getting multiple rows of the same race for different offenses.  So I have a row for school 123 for  23 Hispanic students under offense D.  I also have a row for school 123 for 15 Hispanc students under offense W.  Is there a way to produce 1 row for school 123 for HIspanic students that lists 15 under column offense W and 23 under column offense D?    Do you think I would need to do some type of merging in order to get a one row record per race?

[art297 · Accepted solution]

sas_new: I haven't kept up with this thread, thus may be missing something, but it sure sounds like you are looking for something like:

data have;

  input (school student race offense) ($);

  cards;

A 12595 WHITE W1

A 1857 ASIAN D1

A 11589 BLACK V1

A 54812 WHITE W1

A 636884 HISPANIC D1

A 115887 HISPANIC D1

A 12595 WHITE W1

A 11589 BLACK V1

B 12595 WHITE W1

B 1857 ASIAN D1

B 11589 BLACK V1

B 54812 WHITE W1

B 636884 HISPANIC D1

B 115887 HISPANIC D1

B 12595 WHITE W1

B 11589 BLACK V1

;

ods output crosstabfreqs =  xtab;

Proc Freq data=have;

  tables school*race*offense/

         out=temp missprint;

run;

proc sort data=xtab;

  by school race offense;

run;

proc transpose data=xtab (where=

        (not(missing(offense) or missing(race))))

         out=want (drop=_:);

  var frequency;

  by school race;

  id offense;

run;

[SAS_new]

Thank you!  The results are exactly what I am looking for.   I am not familiar with how to read this code.  Can you help me interpret:

proc transpose data=xtab (where=

        (not(missing(offense) or missing(race))))

         out=want (drop=_:);

[art297]

Proc transpose was used to bring each set of related records together on one record.  The original output file, from the ODS crosstabfreqs table includes summary lines that, in this case, would have missing values for the offense and race variables.  As such, I used a where statement to exclude those records.

The statement ended with an out=want (drop=_:); clause in order to omit/drop the automatic variables in the file that begin with an underscore as they weren't relevant to your desired output.

[SAS_new]

I tried to supress the noprint here on the Proc Freq code due to the large size of the dataset.  However, the crosstabfreqs code does not work with the 'noprint' option.  Is there a way around it?

ods output crosstabfreqs = xtab; run;

proc freq susp  noprint;

     tables schoolcode*race*offense/out=temp missprint; run;

proc sort data=xtab;

     by schoolcode*race*offense; run;

[Reeza]

You don't need the odsoutput, xtab dataset. Use the temp dataset instead, its basically the same thing.

I'm not sure why Art did both....in fact he gets rid of the extra data you get with the not missing functions in the next step.

Proc Freq data=have noprint;

  tables school*race*offense/

         out=temp sparse missprint;

run;

proc transpose data=temp (where=

        (not(missing(offense) or missing(race))))

         out=want (drop=_:);

  var count;

  by school race;

  id offense;

run;

[SAS_new]

Worked perfectly!  Thank you!