Desktop productivity for business analysts and programmers

Converting a list of data into specific please!

Posts: 1

Converting a list of data into specific please!


I am new user of Enterprise Guide. I have an Excel file with a list of titles (i.e., manager, assistant manager, etc.) in column A, and a list of salaries in column B.

I have another Excel File that has a list of salary ranges (i.e., $40,000 to $50,000).

I would like to know if I can bring my first file (the list of titles and salaries) into Enterprise Guide and separate each row into one of the ranges. For instance, how many directors' salaries' range from $40,000 to $50,000)?

With this output from EG, I would like to be able to copy it into the second Excel file as I need to submit this file as part of a survey.

Thank you for any help or suggestions you may offer!
SAS Employee
Posts: 149

Re: Converting a list of data into specific please!

There are a number of possibilities here. Step 1 would definitely be getting your file with titles and saleries imported as a SAS data set. From there, I would probably create a format (under Tasks > Data > Create Format) which contains the Salary Ranges from your second Excel file. In the options pane, make sure you're saving the format to a permanent library, like SASUSER. In the Define formats pane, create labels such as $0 to $25K, $25K to $40K, etc. and for each label select a salary range which matches information for that label.

With the format in hand, you can now run a One-Way Frequencies task with Salary as the analysis variable and Title as the Group by variable. Format the Salary column with the format you just created, and it will now show you, for each title, how many folks fall into the $40K to $50K range and so on. If you choose to create a SAS data set from One-Way Freqencies (Results pane), then you can use Send to or Export to get an Excel workbook with those values.

Unfortunately, there's no easy point and click way to turn the data set with salary ranges into a format. There are coding ways to do this, so if the salary ranges were changing all the time, know that there are more advanced methods to avoid redoing things manually.

If you didn't want to use a custom format, one alternative approach would be to join the Salary/Title information with the Range information and use summarization in the Query builder to generate the counts. You'd really have to know what you're doing however since it would involve joins that are not on equality. EG can handle this... but it's unusual and can be tricky to figure out.
Frequent Contributor
Posts: 82

Re: Converting a list of data into specific please!

Could you please show with some example lines how your data look like and what you want to get?

It sounds like you have to use Proc Format.
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation