BookmarkSubscribeRSS Feed
cmjes
Calcite | Level 5
Hello:

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!
2 REPLIES 2
RichardH_sas
SAS Employee
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.
ieva
Pyrite | Level 9
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 598 views
  • 0 likes
  • 3 in conversation