SAS Project 5: General....SAS Technical Problem to solve....DIY

Here's a little Complex SAS Reporting Example to work with... 

One of the Adhoc Reporting groups at a company maintains a MS Access Database of all the Internal and External Dept requests that are worked by a Data Analysts team. Now the Management team of that group needs a summary Productivity report on the team performance in an Excel Report.

MS Access Database has 6-7 tables containing Normalized tables of the Work performed by the group…Screenshots of the tables are shown below….

Since the Report Requests is huge...showing just the layout of the table…

Download Sample MS Access database here

Requirements

1. Excel Report should have 4-6 Tabs (depending on data) with different layout of reports…Each Tab should have the totals by the current month (till date) and Year to date Information…The Code should be flexible enough to run for historic data as well…i.e. last years May Month as well.

  

1. Tab #1 Analyst - Number of Requests worked on by Analysts.

1. Tab #2 Report Category - Number of Requests worked on by Analysts per Report Category… The Request Categories should be grouped as External or Internal Requests based on the criteria below.

1. Tab #3 Analyst by Report Category – Show ALL Requests Completed Count by Analysts on the Column.

1. Tab #4 Analyst by Dept Name/ Dept Head - Show External Requests Number of Requests worked on by Analysts per Dept Name/ Dept Head.

1. Tab #5 Open Requests – Show ALL requests that are Not Completed by Analysts and their details.

1. Tab #5 ALL Requests Whose Details were Not Insufficiently filled and require completion. Number of Requests worked on by Analysts per Report Category details…Count of Reasons what info is missing…Reasons are identified by these conditions….

Download ALL Project files here

Check your solution with my Solution at here

 ..under Projects | Mini Projects 2 Try...

SAS Project 4: Govt....SAS Technical Problem to solve....DIY

SAS Technical Screening Problem asked at CDC, Atlanta…

The following 3 datasets exist in a hypothetical system:

COUNTY

COUNTY_ID	STATE_NAME	COUNTY_NAME
1	Texas	Collin
2	Texas	Dallas
3	Georgia	DeKalb

AGE_DISTRIBUTION

COUNTY_ID	AGE_0_10	AGE_10_20	AGE_20_40	AGE_40_PLUS
1	100	20	40	60
2	10	10	40	50
3	45	100	56	67

AGE_DISTRIBUTION_DESC

CATEGORY_NAME	CATEGORY_DESCRIPTION
AGE_0_10	< 10 years
AGE_10_20	Between 10 and 20 years
AGE_20_40	Between 20 and 40 years
AGE_40_PLUS	> 40 years

Requirement 1: Write a SAS program (with ODS) to use the datasets given above to generate HTML and MS EXCEL file that will look like the following (the files must have the 3 tables given below):

County Name	Age Distribution
	Category	Total Number
Collin	< 10 years	100
	Between 10 and 20 years	20
	Between 20 and 40 years	40
	> 40 years	60
Dallas	< 10 years	10
	Between 10 and 20 years	10
	Between 20 and 40 years	40
	> 40 years	50
DeKalb	< 10 years	45
	Between 10 and 20 years	100
	Between 20 and 40 years	56
	> 40 years	67

Category	County
	Collin	Dallas	DeKalb
< 10 years	100	10	45
Between 10 and 20 years	20	10	100
Between 20 and 40 years	40	40	56
> 40 years	60	50	67
Total	220	110	268

Category	State
	Texas	Georgia
< 10 years	110	45
Between 10 and 20 years	30	100
Between 20 and 40 years	80	56
> 40 years	110	67
Total	330	268

Note: The color scheme and other style elements (bold, italics etc) MUST be incorporated in the output.

Requirement 2: With a different dataset structure as given below, produce the same HTML and MS EXCEL file result as given above:

COUNTY

COUNTY_ID	STATE_NAME	COUNTY_NAME
1	Texas	Collin
2	Texas	Dallas
3	Georgia	DeKalb

AGE_DISTRIBUTION

COUNTY_ID	AGE_DISTR_CAT_ID	DISTRIBUTION_COUNT
1	1.1	100
1	2.2	20
1	3.3	40
1	4.4	50
2	1.1	10
2	2.2	10
2	3.3	40
2	4.4	50
3	1.1	45
3	2.2	100
3	3.3	56
3	4.4	67

AGE_DISTRIBUTION_DESC

AGE_DISTR_CAT_ID	CATEGORY_DESCRIPTION
1.1	< 10 years
2.2	Between 10 and 20 years
3.3	Between 20 and 40 years
4.4	> 40 years

What to submit: Please provide two separate self-contained SAS Programs (datasets and data in these datasets must be declared in the ‘DATA’ step of the programs, an external data source should not be used) which can be executed in the SAS console. The programs must generate a HTML file called ‘results.html’ and an MS Excel file called ‘results.xls’, the look and feel of which has been depicted earlier.

The following aspects must be kept in mind when developing the programs:
•Presentation: The generated HTML/MS Excel must resemble the output table structure given above.
•Program efficiency: The program must be as short and efficient as possible.
•In each program a macro variable must be used to determine whether to produce HTML output or MS-Excel output. We will alter the variable value (the possible values must be documented in the program) during execution to generate the desired output.

SAS Project 3: Retailing Industry....SAS Technical Problem to solve....DIY

This SAS Screening Problem was asked at Gap Inc…

The following shows 6 sample records of a large SAS data set.
The data set name is trans

CustomerID	transactiondate	amount	category
9801234	10/01/1998	123.98	toys
9802234	12/10/1997	80.34	books
9802234	12/10/1997	100.00	apparel
9805556	08/01/1996	22.90	toys
9805556	09/10/1996	25.50	apparel
9805556	10/11/1996	18.90	books
9801134	11/11/1999	12.11	toys

Write some SAS code that that will answer the questions below using the SAS file above.

These questions get at your understanding of different levels of summarization and in more general terms, how you approach programming problems. There are many ‘right’ answers.

You do not have to use the original dataset as input to each question, you may want to summarize at one step and use that summarization in later steps.

1. Total and average amount spent by category
2. Which category has the highest average purchase
3. What is the average number of categories that customers purchase
4. What is the average and total amount by customer
5. What is the average number of days between purchases (as of today)