3.1 : Use the data in the spreadsheet test.xls to do the following: i. Read the data from this file into a SAS data set. Note that there are missing data values "NA" in the spreadsheet and a code "S" that you can treat as unknown. ii. Use DATA step programming to produce a data set that has the following variables: year merchandise_line total Ecommerce iii. Construct a new variable EcommercePCT = Ecommerce/total * 100. iv. Plot EcommercePCT vs. year. Look at a SERIES plot in PROC SGPLOT with GROUP=merchandise_line. Question 3.2: Take the data set from part ii in problem 3.1 and do the following: i. Use PROC SQL to construct EcommercePCT ii. Construct a table view of "Music and videos" merchandise where you display Year Total Ecommerce and EcommercePCT Order the table rows by year. iii. Produce a SAS data set from SQL that includes only 'Books and magazines' and 'Music and videos'. Print this data set. Question 3.3 : Data on three groups of subjects are arranged so that a group code (C, A, B) is followed by one or more scores for that group which can span more than one record of raw data. Write a program to read these data and create a SAS data set with variables GROUP and SCORE, one set per observation. Generate an error message if you encounter an invalid group code (e.g. GROUP = \"D\") and ignore numeric data until the next valid group code. Sample data areC 303 102 150 B 202 C 300 B 450 400 399420 A 289 280 278 D 14 16 C 27 38 Hint: Read every data item in the raw data file as a character value and test if it is an 'A', 'B' or 'C'. If it is one of those values, set GROUP equal to that value and then read the numeric data. If not, convert the character (numeric data) you just read to a number using the INPUT function, e.g. SCORE = INPUT(CHARVAR,5.); would accomplish this. Based on HW 8-2 from Cody and Pass. U.S. Electronic Shopping and Mail-Order Houses (NAICS 45411) - Total and E-commerce Sales by Merchandise Line 1: 2013-1999 [Estimates are based on data from the Annual Retail Trade Survey. Sales estimates are shown in millions of dollars, consequently industry group estimates may not be additive.] Value of Sales 2012 Revised 2013 Merchandise Lines Total Electronic Shopping and Mail-Order Houses (NAICS 45411) Books and magazines Clothing and clothing accessories (includes footwear) Computer hardware Computer software Drugs, health aids, and beauty aids Electronics and appliances Food, beer, and wine Furniture and home furnishings Music and videos Office equipment and supplies Sporting Goods Toys, hobby goods, and games Other merchandise2 Nonmerchandise receipts3 2011 Revised 2010 Revised 2009 Revised 2008 Revised E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce 348,126 11,381 46,545 26,299 8,291 90,742 26,546 7,365 23,158 11,185 8,067 9,749 8,704 47,798 22,296 219,417 10,210 40,032 14,731 5,410 17,026 22,753 5,153 20,030 10,253 S 7,844 S 33,981 18,354 325,817 10,883 40,044 25,569 8,021 90,277 26,310 7,002 19,325 9,954 8,502 8,373 7,547 43,874 20,136 193,583 9,651 33,248 14,226 5,365 14,540 22,067 4,876 15,984 8,984 6,794 6,245 5,923 29,755 15,925 293,582 10,136 35,090 24,873 6,837 82,148 24,562 5,969 17,030 8,335 8,113 6,962 6,575 39,433 17,519 168,544 8,740 28,102 13,917 4,476 11,798 20,899 3,969 13,763 7,335 6,405 5,083 5,162 25,318 13,577 262,912 8,818 31,317 22,350 5,810 75,141 21,639 5,247 14,580 S 7,792 6,413 5,863 35,282 15,494 142,943 7,406 23,424 12,043 3,665 10,918 17,809 3,361 11,322 S 5,992 4,394 4,389 20,915 11,192 235,352 7,543 27,267 20,102 5,418 69,076 18,743 4,812 12,814 6,159 7,711 6,042 5,266 30,961 13,438 121,193 5,989 19,698 11,200 3,433 8,847 15,059 2,960 9,387 5,116 5,963 3,936 3,585 16,666 9,354 229,153 7,628 25,535 20,904 4,898 64,752 17,427 4,884 13,353 5,360 8,173 5,815 5,576 31,380 13,468 114,732 5,595 17,627 11,384 2,885 7,580 13,658 3,031 9,639 4,245 6,421 3,532 3,408 16,402 9,325 223,681 7,177 24,314 22,846 4,593 61,854 15,976 5,129 13,483 4,666 8,093 5,562 5,352 31,200 13,436 105,613 5,372 16,045 11,697 2,613 6,475 11,960 2,996 9,131 3,404 5,925 3,179 3,091 14,782 8,943 NA Not Available S - Estimate does not meet publication standards because of high sampling variability (coefficient of variation is greater than 30%), poor response quality (total quantity response rate is less than 50%), or other concerns about the estimate's quality. Unpublished estimates derived from this table by subtraction are subject to these same limitations and should not be attributed to the U.S. Census Bureau. For a description of publication standards and the total quantity response rate, see http://www.census.gov/quality/standards/standardf1.html. Note: Estimates are not adjusted for price changes. Additional information on confidentiality protection, sampling error, nonsampling error, sample design, and definitions can be found on the Internet at . 1 2 3 Estimates include data for businesses with or without paid employees and are grouped according to merchandise categories used in the Annual Retail Trade Survey. Includes other merchandise such as collectibles, souvenirs, auto parts and accessories, hardware, lawn and garden equipment and supplies, and jewelry. Includes nonmerchandise receipts such as auction commissions, customer training, customer support, advertising, and shipping and handling. Source: U.S. Census Bureau, Annual Retail Trade Survey 2006 Revised 2007 Revised Total 2005 Revised Total E-Commerce 202,251 5,976 21,076 20,171 3,843 63,301 13,916 4,212 12,049 3,885 6,957 4,499 4,600 26,448 11,318 86,025 3,695 12,896 10,376 2,023 5,417 9,939 2,281 7,892 2,617 4,920 2,403 2,524 12,002 7,040 2004 2003 2002 2001 2000 1999 Total E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce Total E-commerce 175,900 5,435 18,562 20,598 3,705 49,812 10,950 3,406 10,584 3,421 6,111 4,177 4,632 25,183 9,324 68,146 2,904 9,466 9,347 1,834 4,270 8,084 1,609 5,633 1,869 3,853 1,912 2,235 9,848 5,282 154,157 4,930 15,681 20,277 3,089 43,338 9,277 2,520 8,515 3,591 S 3,579 4,133 21,632 8,242 52,996 2,270 7,054 8,006 1,456 3,574 S 1,030 4,023 1,480 S 1,430 1,758 7,152 4,379 134,304 4,446 14,497 18,366 2,930 34,783 7,151 2,172 8,057 3,473 5,194 3,053 4,049 19,357 6,776 41,962 1,994 5,501 6,580 1,192 S 4,109 820 3,283 1,384 2,929 1,155 1,610 5,548 3,251 122,214 4,267 14,477 18,718 3,661 27,471 5,520 2,014 7,290 3,902 5,099 3,073 3,775 17,539 5,408 33,764 1,787 4,545 6,079 1,239 1,849 2,704 714 2,412 1,352 2,285 1,028 1,259 4,062 2,449 114,749 4,137 15,712 21,128 3,983 19,707 4,535 2,037 6,696 4,083 5,740 1,906 3,138 17,676 4,271 25,892 1,644 3,325 5,419 1,157 1,122 1,844 519 1,654 1,211 1,824 517 896 2,997 1,763 113,790 4,297 15,415 27,221 3,706 15,557 3,641 1,960 6,556 4,433 6,600 1,794 3,174 16,249 3,187 21,221 1,759 2,262 6,049 1,109 701 1,128 591 1,006 1,154 1,375 409 806 1,866 1,006 94,282 3,434 13,535 23,846 2,809 11,557 2,709 1,429 5,822 4,250 7,284 NA 2,162 NA 1,126 11,615 1,439 909 4,166 755 239 504 235 350 762 592 NA 383 NA 304