topic Re: Using proc summary how can I flatten data? in New SAS User

Using proc summary how can I flatten data?

helloagainoh2 — Wed, 28 Jul 2021 16:11:25 GMT

Hi I have some data that I am using proc summary to tidy up so I can output it as a text file.

I am having some issues, for the text file they want both male and female counts on the same line, however when using proc summary when I use the variable gender in the class statement it separates them.

proc summary data=undup nway;

class gender Class Ethnic ;

var count;

format Ethnic $ethnic.;

output out=undupFinal sum=;

run;

I like the format that proc summary gives me but I would like to be able to have both the male count and female count on the same line. Is there a way to do this with proc summary?

Re: Using proc summary how can I flatten data?

Reeza — Wed, 28 Jul 2021 16:17:11 GMT

No, but you can use PROC REPORT, TABULATE or wrangle the data after the fact. It depends on what exactly you need going to a text file.

Can we assume you're familiar with ODS and passing your results directly to an Excel, PDF or Word file?

Re: Using proc summary how can I flatten data?

PaigeMiller — Wed, 28 Jul 2021 16:18:54 GMT

It's not clear why you would want them both on one line, but in general, the idea of converting a long data set (which is what PROC SUMMARY gives you) into a much wider data set where everything is on one line, is generally considered to be poor practice, and makes your coding much more difficult. See Maxim 19, Long beats Wide.

So why do you want this? What are you going to do with this wide and flat data that you can't do with the actual PROC SUMMARY output?

Re: Using proc summary how can I flatten data?

helloagainoh2 — Wed, 28 Jul 2021 16:24:34 GMT

Yes I am familiar with ODS and outputting to PDF and Excel. I inherited this program and the previous coder used proc summary to group and aggregate the data by categories and then output it to a text file.

They didn't need to report different counts for genders at the time so I am not sure proc summary is the best way forward. I am realizing that I need to have one line with counts for the different combinations of gender and ethnicity.

The output needs to be in a certain format, so for example the output would be a number for white females that starts at column 35 and the output for Hispanic men would be a number at column 53 on the text file.

Re: Using proc summary how can I flatten data?

helloagainoh2 — Wed, 28 Jul 2021 16:37:25 GMT

It's for an automated service that reads the text file for verification, they want the data on the text file outputted on certain columns at certain lengths.

Re: Using proc summary how can I flatten data?

Reeza — Wed, 28 Jul 2021 16:53:59 GMT

Yeah, then you may as well wrangle the output from PROC SUMMARY. I would take a look at some of the macros JeffMyers has provided in the Community Library if you want tables that are a little more structured or to help get it in a format automatically but I trust that you're capable of figuring out a PROC TRANSPOSE as well.

Re: Using proc summary how can I flatten data?

ballardw — Wed, 28 Jul 2021 17:07:37 GMT

@helloagainoh2 wrote:

Yes I am familiar with ODS and outputting to PDF and Excel. I inherited this program and the previous coder used proc summary to group and aggregate the data by categories and then output it to a text file.

They didn't need to report different counts for genders at the time so I am not sure proc summary is the best way forward. I am realizing that I need to have one line with counts for the different combinations of gender and ethnicity.

The output needs to be in a certain format, so for example the output would be a number for white females that starts at column 35 and the output for Hispanic men would be a number at column 53 on the text file.

It may help to provide the full description of the text file.

When specific columns positions are needed that quite often calls for a Data step to full control and might require transposing the output from Summary before creating the output text file.

Re: Using proc summary how can I flatten data?

Tom — Thu, 29 Jul 2021 00:23:15 GMT

@helloagainoh2 wrote:

Yes I am familiar with ODS and outputting to PDF and Excel. I inherited this program and the previous coder used proc summary to group and aggregate the data by categories and then output it to a text file.

They didn't need to report different counts for genders at the time so I am not sure proc summary is the best way forward. I am realizing that I need to have one line with counts for the different combinations of gender and ethnicity.

The output needs to be in a certain format, so for example the output would be a number for white females that starts at column 35 and the output for Hispanic men would be a number at column 53 on the text file.

No need to convert the data before writing the text file.

Say you have a variable GROUP, SEX, RACE and COUNT and the data is sorted by GROUP then you can write all of the values for the same group to the same line. Just use the trailing @ on the PUT statement.

data _null_;
  set have;
  by group;
  file 'myfile.txt';
  ...
  if sex='FEMALE' and RACE='WHITE' then put @35 count @;
  ...
  if last.group then put;
run;