All data for the orion library are in the sql directory

1. Modifying the Table Structure and the Table Data

Marketing determined that the reason that product 210200200022 is not selling well is because it is named “Sunfit Slow Swimming Trunks.”

Change the product name and packaging to Sunfit Speedy Swimming Trunks.

a. Product names are on the table orion.product_dim. Create a table work.products that contains only rows with distinct product_id. Reset the number of output observations to 5 and display the first five observations on work.products.

In [13]:
Out[13]:
SAS Output
Product ID Product Line Product Category Product Group Product Name Supplier Country Supplier Name Supplier ID
210200100009 Children Children Sports A-Team, Kids Kids Sweat Round Neck,Large Logo US A Team Sports 3298
210200100017 Children Children Sports A-Team, Kids Sweatshirt Children's O-Neck US A Team Sports 3298
210200200022 Children Children Sports Bathing Suits, Kids Sunfit Slow Swimming Trunks US Nautlius SportsWear Inc 6153
210200200023 Children Children Sports Bathing Suits, Kids Sunfit Stockton Swimming Trunks Jr. US Nautlius SportsWear Inc 6153
210200300006 Children Children Sports Eclipse, Kid's Clothes Fleece Cuff Pant Kid'S US Eclipse Inc 1303

b. Write a query to update work.Products by setting Product_Name to Sunfit Speedy Swimming Trunks where Product_ID=210200200022. Check your answer by including a query to display the row for which Product_ID=210200200022.

In [14]:
Out[14]:
SAS Output
Product ID Product Line Product Category Product Group Product Name Supplier Country Supplier Name Supplier ID
210200200022 Children Children Sports Bathing Suits, Kids Sunfit Speedy Swimming Trunks US Nautlius SportsWear Inc 6153

c. Orion Star wants to add a new line of snorkeling products. Add the following rows to the table work.Products.The values you will need for columns Product_ID,Product_Line,Product_Category,Product_Group, Product_Name,Supplier_Country,Supplier_Name,and Supplier_ID are:

  240600100202,"Sports","Swim Sports","Snorkel Gear",
     "Coral Dive Mask - Med","AU","Dingo Divers",21001
  240600100203,"Sports","Swim Sports","Snorkel Gear",
     "Coral Dive Mask - Large","AU","Dingo Divers",21001
  240600100212,"Sports","Swim Sports","Snorkel Gear",
     "Coral Dive Fins - Med","AU","Dingo Divers",21001
  240600100213,"Sports","Swim Sports","Snorkel Gear",
     "Coral Dive Fins - Large","AU","Dingo Divers",21001
  240600100222,"Sports","Swim Sports","Snorkel Gear",
     "Coral Advanced Snorkel","AU","Dingo Divers",21001
  240600100223,"Sports","Swim Sports","Snorkel Gear",
     "Coral Pro Snorkel","AU","Dingo Divers",21001

Check your results by including a query that lists rows for which product_group is Snorkel Gear

In [15]:
Out[15]:
SAS Output
Product ID Product Line Product Category Product Group Product Name Supplier Country Supplier Name Supplier ID
240600100202 Sports Swim Sports Snorkel Gear Coral Dive Mask - Med AU Dingo Divers 21001
240600100203 Sports Swim Sports Snorkel Gear Coral Dive Mask - Large AU Dingo Divers 21001
240600100212 Sports Swim Sports Snorkel Gear Coral Dive Fins - Med AU Dingo Divers 21001
240600100213 Sports Swim Sports Snorkel Gear Coral Dive Fins - Large AU Dingo Divers 21001
240600100222 Sports Swim Sports Snorkel Gear Coral Advanced Snorkel AU Dingo Divers 21001
240600100223 Sports Swim Sports Snorkel Gear Coral Pro Snorkel AU Dingo Divers 21001

2. Modifying the Table Structure and the Table Data

a. Use the following program to create the tables work.Products and work.NewProducts

In [16]:
proc sql;
   create table work.Products as
      select distinct * 
      from orion.Product_DIM
      order by Product_ID
   ;
   create table work.NewProducts
      (Product_ID num label='Product ID',
       Product_Line char(20) label='Product Line',
       Product_Category char(25) label='Product Category',
       Product_Group char(25) label='Product Group',
       Product_Name char(45) label='Product Name',
       Supplier_Country char(2) label='Supplier Country',
       Supplier_Name char(30) label='Supplier Name',
       Supplier_ID num label='Supplier ID'
      );
   insert into work.NewProducts(Product_ID,Product_Line, 
               Product_Category, Product_Group, Product_Name,
               Supplier_Country, Supplier_Name, Supplier_ID)
      values(240600100202,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Mask - Med","AU","Dingo Divers",21001)
      values(240600100203,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Mask - Large","AU","Dingo Divers",21001)
      values(240600100212,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Fins - Med","AU","Dingo Divers",21001)
      values(240600100213,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Fins - Large","AU","Dingo Divers",21001)
      values(240600100222,"Sports","Swim Sports","Snorkel Gear",
             "Coral Advanced Snorkel","AU","Dingo Divers",21001)
      values(240600100223,"Sports","Swim Sports","Snorkel Gear",
             "Coral Pro Snorkel","AU","Dingo Divers",21001)
      values(240600100341,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Skin - Small","AU","Dingo Divers",21001)
      values(240600100342,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Skin - Med","AU","Dingo Divers",21001)
      values(240600100343,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Skin - Large","AU","Dingo Divers",21001)
      values(240600100351,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Goody Bag - Large","AU","Dingo Divers",21001)
      values(240600100352,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Knife","AU","Dingo Divers",21001)
      values(240600100361,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Duffel - Small","AU","Dingo Divers",21001)
      values(240600100362,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Duffel - Med","AU","Dingo Divers",21001)
      values(240600100363,"Sports","Swim Sports","Snorkel Gear",
             "Coral Dive Duffel - Large","AU","Dingo Divers",21001)
      ;
quit;
Out[16]:

339  ods listing close;ods html5 file=stdout options(bitmap_mode='inline') device=png; ods graphics on / outputfmt=png;
NOTE: Writing HTML5 Body file: STDOUT
340  
341  proc sql;
342     create table work.Products as
343        select distinct *
344        from orion.Product_DIM
345        order by Product_ID
346     ;
NOTE: Data file ORION.PRODUCT_DIM.DATA is in a format that is native to another host, or the file encoding does not match the 
      session encoding. Cross Environment Data Access will be used, which might require additional CPU resources and might reduce 
      performance.
NOTE: Table WORK.PRODUCTS created, with 481 rows and 8 columns.

347     create table work.NewProducts
348        (Product_ID num label='Product ID',
349         Product_Line char(20) label='Product Line',
350         Product_Category char(25) label='Product Category',
351         Product_Group char(25) label='Product Group',
352         Product_Name char(45) label='Product Name',
353         Supplier_Country char(2) label='Supplier Country',
354         Supplier_Name char(30) label='Supplier Name',
355         Supplier_ID num label='Supplier ID'
356        );
NOTE: Table WORK.NEWPRODUCTS created, with 0 rows and 8 columns.
357     insert into work.NewProducts(Product_ID,Product_Line,
358                 Product_Category, Product_Group, Product_Name,
359                 Supplier_Country, Supplier_Name, Supplier_ID)
360        values(240600100202,"Sports","Swim Sports","Snorkel Gear",
361               "Coral Dive Mask - Med","AU","Dingo Divers",21001)
362        values(240600100203,"Sports","Swim Sports","Snorkel Gear",
363               "Coral Dive Mask - Large","AU","Dingo Divers",21001)
364        values(240600100212,"Sports","Swim Sports","Snorkel Gear",
365               "Coral Dive Fins - Med","AU","Dingo Divers",21001)
366        values(240600100213,"Sports","Swim Sports","Snorkel Gear",
367               "Coral Dive Fins - Large","AU","Dingo Divers",21001)
368        values(240600100222,"Sports","Swim Sports","Snorkel Gear",
369               "Coral Advanced Snorkel","AU","Dingo Divers",21001)
370        values(240600100223,"Sports","Swim Sports","Snorkel Gear",
371               "Coral Pro Snorkel","AU","Dingo Divers",21001)
372        values(240600100341,"Sports","Swim Sports","Snorkel Gear",
373               "Coral Dive Skin - Small","AU","Dingo Divers",21001)
374        values(240600100342,"Sports","Swim Sports","Snorkel Gear",
375               "Coral Dive Skin - Med","AU","Dingo Divers",21001)
376        values(240600100343,"Sports","Swim Sports","Snorkel Gear",
377               "Coral Dive Skin - Large","AU","Dingo Divers",21001)
378        values(240600100351,"Sports","Swim Sports","Snorkel Gear",
379               "Coral Dive Goody Bag - Large","AU","Dingo Divers",21001)
380        values(240600100352,"Sports","Swim Sports","Snorkel Gear",
381               "Coral Dive Knife","AU","Dingo Divers",21001)
382        values(240600100361,"Sports","Swim Sports","Snorkel Gear",
383               "Coral Dive Duffel - Small","AU","Dingo Divers",21001)
384        values(240600100362,"Sports","Swim Sports","Snorkel Gear",
385               "Coral Dive Duffel - Med","AU","Dingo Divers",21001)
386        values(240600100363,"Sports","Swim Sports","Snorkel Gear",
387               "Coral Dive Duffel - Large","AU","Dingo Divers",21001)
388        ;
NOTE: 14 rows were inserted into WORK.NEWPRODUCTS.

389  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
390  
391  ods html5 close;ods listing;

392

b. Orion Star will drop the entire group of Kid’s Eclipse products from the catalog. Delete the rows containing information for these products from the table work.Products. Include a query after droppin these rows to count how many rows contain Kid’s Eclipse as part the the Product_group.

In [17]:
Out[17]:
SAS Output
 
0

c. Add the products in work.NewProducts to the table Products. To check your work, add a query to select Product_Name, Supplier_Name, and Supplier_Country for all rows of work.products that have product_category equal to Swim Sports.

In [18]:
Out[18]:
SAS Output
Product Name Supplier Name Supplier Country
Goggles, Assorted Colours Nautlius SportsWear Inc US
Swim Suit Fabulo Nautlius SportsWear Inc US
Swim Suit Laurel Luna sastreria S.A. ES
Sharky Swimming Trunks Dolphin Sportswear Inc US
Sunfit Luffa Bikini Nautlius SportsWear Inc US
Milan Swimming Trunks Luna sastreria S.A. ES
Pew Swimming Trunks Luna sastreria S.A. ES
Coral Dive Mask - Med Dingo Divers AU
Coral Dive Mask - Large Dingo Divers AU
Coral Dive Fins - Med Dingo Divers AU
Coral Dive Fins - Large Dingo Divers AU
Coral Advanced Snorkel Dingo Divers AU
Coral Pro Snorkel Dingo Divers AU
Coral Dive Skin - Small Dingo Divers AU
Coral Dive Skin - Med Dingo Divers AU
Coral Dive Skin - Large Dingo Divers AU
Coral Dive Goody Bag - Large Dingo Divers AU
Coral Dive Knife Dingo Divers AU
Coral Dive Duffel - Small Dingo Divers AU
Coral Dive Duffel - Med Dingo Divers AU
Coral Dive Duffel - Large Dingo Divers AU

d. Top Sports (Supplier_ID=755) experienced delays of one week in shipping all sleeping bags (Product_Group="Sleepingbags") from the factory. Modify work.Products to add a numeric Shipping_Delay column. Update the column Shipping_Delay in the work.Products table to show a delay of seven days in shipping Top Sports sleeping bags.

Check your work by adding a query that lists Product_Name, Supplier_Name, and Shipping_Delay for all rows of work.products that do not have missing values.

In [19]:
Out[19]:
SAS Output

Product Shipping Delays

Product Name Supplier Name Shipping_Delay
Basic 10, Left , Yellow/Black Top Sports 7
Expedition Zero,Medium,Left,Charcoal Top Sports 7
Expedition Zero,Medium,Right,Charcoal Top Sports 7
Expedition Zero,Small,Left,Charcoal Top Sports 7
Expedition Zero,Small,Right,Charcoal Top Sports 7
Expedition10,Medium,Right,Blue Ribbon Top Sports 7
Expedition 10,Small,Left,Blue Ribbon Top Sports 7
Expedition 10,Small,Right,Blue Ribbon Top Sports 7
Expedition 20,Large,Right,Forestgreen Top Sports 7
Expedition 20,Medium,Right,Forestgreen Top Sports 7
Outback Sleeping Bag, Large,Left,Blue/Black Top Sports 7
Outback Sleeping Bag, Large,Right, Blue/Black Top Sports 7

e. Remove the column Shipping_Delay from the work.Products table. Use a proc contents step to check that shipping_delay is no longer in the table.

In [20]:
Out[20]:
SAS Output

The CONTENTS Procedure

Data Set Name WORK.PRODUCTS Observations 490
Member Type DATA Variables 8
Engine V9 Indexes 0
Created 02/09/2017 15:58:00 Observation Length 168
Last Modified 02/09/2017 15:58:00 Deleted Observations 0
Protection   Compressed NO
Data Set Type   Sorted NO
Label      
Data Representation SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64    
Encoding utf-8 Unicode (UTF-8)    
Engine/Host Dependent Information
Data Set Page Size 65536
Number of Data Set Pages 2
First Data Page 1
Max Obs per Page 389
Obs in First Data Page 372
Number of Data Set Repairs 0
Filename /tmp/SAS_work138000005650_localhost.localdomain/products.sas7bdat
Release Created 9.0401M4
Host Created Linux
Inode Number 536409
Access Permission rw-r--r--
Owner Name sasdemo
File Size 192KB
File Size (bytes) 196608
Alphabetic List of Variables and Attributes
# Variable Type Len Format Label
3 Product_Category Char 25   Product Category
4 Product_Group Char 25   Product Group
1 Product_ID Num 8 12. Product ID
2 Product_Line Char 20   Product Line
5 Product_Name Char 45   Product Name
6 Supplier_Country Char 2   Supplier Country
8 Supplier_ID Num 8 12. Supplier ID
7 Supplier_Name Char 30   Supplier Name

f. Delete both the work.Products and work.NewProducts tables. Use a query with the dictionary.tables table to count the number of tables in the work library that are named either Products or NewProducts.

In [21]:
Out[21]:
SAS Output
 
0