BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
terryreid314
Fluorite | Level 6

Hello everyone,

 

I need help to create a variable that contains the maximum earning in the Microsoft® Excel® spreadsheet Column A labels. As an example, for label $2,500 to $4,999, the variable would include value 4,999. This is in addition to the variable that contains the associated population number.

 

I have to store that data in a raw data file, then sort the data from highest amount earned to lowest amount earned. Store that sorted data in a second raw data file. Perform another sort to order the data from highest population number to lowest population number. Store that sorted data in a third raw data file. I must keep the two related data variables associated so that after each sort the amount earned is associated with the population number or the number of people that earned that amount. I have attached the spreadsheet with the data mentioned above.

 

Using SAS 9.4 and Excel 2013

1 ACCEPTED SOLUTION

Accepted Solutions
terryreid314
Fluorite | Level 6

Ballardw,

 

Thank you for the motivation. After you told me to show you the dataset for my code. I just kept pulling pieces of the project together until I got a working code. My code for this assignment is below:

 

/* Generated Code (IMPORT) */
/* Source File: Guildford(TR)2MaximumEarning.xlsx */
/* Code generated on: 2/10/19, 12:49 PM */

%web_drop_table(WORK.IMPORT1);


FILENAME REFFILE '/home/terryreid3140/Guildford(TR)2MaximumEarning.xlsx';

PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT1;
GETNAMES=YES;
RUN;

DATA Female_Earnings;
SET Work.Import1;
WHERE Gender='Female' and Other='Yes';

Run;

PROC CONTENTS DATA=WORK.Female_Earnings;
RUN;

PROC PRINT DATA=WORK.female_earnings;
*The above code provides data on females who worked full-time and year round in Guilford County, NC.;
TITLE 'Females Who Worked Full-time and Year-round';
RUN;


*This code provides the sorted Population for each earning category for females who worked full-time and year round in Guilford County, NC.;
PROC SORT Data=Work.Female_Earnings OUT=Work.Female_Earnings_Sort;
by Descending Population;
RUN;
POC PRINT DATA=WORK.Female_Earnings_Sort;
TITLE 'Female Earnings Sorted by Population';
VAR 'Earning Range Low'n 'Earning Range High'n 'Maximum Earning'n Population County Gender;
RUN;


*This code provides the sorted maximum earnings for the females who worked full-time and year round in Guilford County, NC.;
PROC SORT Data=Work.Female_Earnings OUT=Work.Female_Earnings_Sort_Earning;
by Descending 'Maximum Earning'n;
RUN;

POC PRINT DATA=WORK.Female_Earnings_Sort_Earning;
TITLE 'Female Earnings Sorted by Maximum Earnings';
VAR 'Earning Range Low'n 'Earning Range High'n 'Maximum Earning'n Population County Gender;
RUN;

%web_open_table(WORK.IMPORT1);

 

View solution in original post

2 REPLIES 2
ballardw
Super User

Do you have a SAS data set yet? That's the first step.

 

Please describe in a bit more detail what you mean by "Store that sorted data in a second raw data file".

 

What do you do after sorting? There are many operations available in SAS that do not actually require sorts.

 

This sounds like homework for a class. If that is the case show us what you have attempted, code and if you have errors log results, and what doesn't seem correct to you.

 

Post code or log results by copy from your SAS session and paste into a code box opened on the forum using the {I} icon.

terryreid314
Fluorite | Level 6

Ballardw,

 

Thank you for the motivation. After you told me to show you the dataset for my code. I just kept pulling pieces of the project together until I got a working code. My code for this assignment is below:

 

/* Generated Code (IMPORT) */
/* Source File: Guildford(TR)2MaximumEarning.xlsx */
/* Code generated on: 2/10/19, 12:49 PM */

%web_drop_table(WORK.IMPORT1);


FILENAME REFFILE '/home/terryreid3140/Guildford(TR)2MaximumEarning.xlsx';

PROC IMPORT DATAFILE=REFFILE
DBMS=XLSX
OUT=WORK.IMPORT1;
GETNAMES=YES;
RUN;

DATA Female_Earnings;
SET Work.Import1;
WHERE Gender='Female' and Other='Yes';

Run;

PROC CONTENTS DATA=WORK.Female_Earnings;
RUN;

PROC PRINT DATA=WORK.female_earnings;
*The above code provides data on females who worked full-time and year round in Guilford County, NC.;
TITLE 'Females Who Worked Full-time and Year-round';
RUN;


*This code provides the sorted Population for each earning category for females who worked full-time and year round in Guilford County, NC.;
PROC SORT Data=Work.Female_Earnings OUT=Work.Female_Earnings_Sort;
by Descending Population;
RUN;
POC PRINT DATA=WORK.Female_Earnings_Sort;
TITLE 'Female Earnings Sorted by Population';
VAR 'Earning Range Low'n 'Earning Range High'n 'Maximum Earning'n Population County Gender;
RUN;


*This code provides the sorted maximum earnings for the females who worked full-time and year round in Guilford County, NC.;
PROC SORT Data=Work.Female_Earnings OUT=Work.Female_Earnings_Sort_Earning;
by Descending 'Maximum Earning'n;
RUN;

POC PRINT DATA=WORK.Female_Earnings_Sort_Earning;
TITLE 'Female Earnings Sorted by Maximum Earnings';
VAR 'Earning Range Low'n 'Earning Range High'n 'Maximum Earning'n Population County Gender;
RUN;

%web_open_table(WORK.IMPORT1);

 

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
  • 2 replies
  • 1254 views
  • 0 likes
  • 2 in conversation