BookmarkSubscribeRSS Feed
helloagainoh2
Calcite | Level 5

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?

7 REPLIES 7
Reeza
Super User
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?

helloagainoh2
Calcite | Level 5

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.

ballardw
Super User

@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.

Tom
Super User Tom
Super User

@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;
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
helloagainoh2
Calcite | Level 5

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. 

Reeza
Super User
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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 803 views
  • 0 likes
  • 5 in conversation