topic Re: Answering questions from an excel data set in SAS Programming

Answering questions from an excel data set

nabeel284 — Mon, 10 Dec 2018 21:30:38 GMT

I have a sas data set (see excel sheet attached) where I need to answer the following 3 questions below....

since there is no month variable in the data I am not able to do a proc means to get highest profit .... any pointers would be appreciated.... thanks.... see attached data set...

Q1) Of the 48 months in the data, which month has the highest total profit? Which month has the highest profit rate? Show your results in a table, and allow two decimals for numbers and percentages. ;

Q2): Of the 12 months of a year, which month has the highest profit and which month has the highest profit rate? Are these two months the same? If not, try to use data to explain why. Similarly, clearly document your tables and format properly. ;

Q3): which product has the highest profit rate in each year? You may need to use the variable sub_category. Create a table that shows the product and its profit rate and the year. Format profit rate to as a percentage with 2 decimal places, e.g., 12.34%;

Re: Answering questions from an excel data set

jdykstra — Mon, 10 Dec 2018 21:33:56 GMT

I don't think individuals will like opening an excel file to view the data. Can you paste a sample of your data just in your question?

Re: Answering questions from an excel data set

Kurt_Bremser — Mon, 10 Dec 2018 21:38:27 GMT

Please:

Post example data in a data step with datalines; see my footnotes for hints.

Post code you already tried.

Post the log from your code.

Re: Answering questions from an excel data set

nabeel284 — Mon, 10 Dec 2018 21:54:38 GMT

there are 48 months in the data , below are the two variables that I think I need from the data

Ship date Profit

11/11/2016 219.582

I guess how do I get month info from **bleep** date...?

Re: Answering questions from an excel data set

nabeel284 — Mon, 10 Dec 2018 21:58:27 GMT

hi kurt

data is quite huge to put in data lines...

I think I need to convert the shipdate variable which is in 11/11/2016 form to month for all the 48 months that are in the data and then see which month has the highest profit.

profit and **bleep**date are variables in the data. profit is numer example 214.32

Re: Answering questions from an excel data set

Kurt_Bremser — Mon, 10 Dec 2018 22:06:01 GMT

The macro from my footnotes lets you specify the number of observations, so you can limit the data step to a number sufficient to illustrate your issue.

Re: Answering questions from an excel data set

jdykstra — Mon, 10 Dec 2018 22:10:40 GMT

data want; set have;

month = month(ship_date); run;

Re: Answering questions from an excel data set

Reeza — Mon, 10 Dec 2018 22:10:06 GMT

Use MONTH/YEAR() to get the month/year from the date variables
Use PROC MEANS to summarize.

You can actually run a single proc means to get all the results in a single step if you know how to deal with the TYPES/WAYS statements.

This is homework, so I'll help, but not provide the answers and your pointers are above. If you need further help post what your code and answer is and a detailed explanation of what is not working.

@nabeel284 wrote:

I have a sas data set (see excel sheet attached) where I need to answer the following 3 questions below....

since there is no month variable in the data I am not able to do a proc means to get highest profit .... any pointers would be appreciated.... thanks.... see attached data set...

Q1) Of the 48 months in the data, which month has the highest total profit? Which month has the highest profit rate? Show your results in a table, and allow two decimals for numbers and percentages. ;

Q2): Of the 12 months of a year, which month has the highest profit and which month has the highest profit rate? Are these two months the same? If not, try to use data to explain why. Similarly, clearly document your tables and format properly. ;

Q3): which product has the highest profit rate in each year? You may need to use the variable sub_category. Create a table that shows the product and its profit rate and the year. Format profit rate to as a percentage with 2 decimal places, e.g., 12.34%;

Re: Answering questions from an excel data set

Reeza — Mon, 10 Dec 2018 22:12:30 GMT

Example of using a format with PROC MEANS to get the desired output.

https://gist.github.com/statgeek/0cae5568752959b035516d6ac07a20fb

Re: Answering questions from an excel data set

nabeel284 — Tue, 11 Dec 2018 15:40:16 GMT

thank you all - I was able to solve the problem... using proc means and the month function to change the date format to month....