Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

managing the data for drilling up and down

Posts: 50

managing the data for drilling up and down

Hi Experts,

I have a data like mentioned below. Where in, I want to add an additional category called channel, which is a break up sale type for a day.

The problem here is, the channel type is not fix count for a store, it varies 4 to 6 and depends upon store to store so how can I manage the sale for a day on inclusion of this Channel type.

We can assume all six channel type as A, B, C, D, E, F.

Earlier I added Day Part which was a fix count so we just devided by 4 on the sale value to balance the store-wise sale for a day.

Please advise and help me as I have huge pressure to meet the deadlines.



18-May-15High StreetNorth & CentralDelhiDelhi1001Breakfast1051427.12377531.35
18-May-15High StreetNorth & CentralDelhiDelhi1001Lunch2607541226183.819910780.29
18-May-15High StreetNorth & CentralDelhiDelhi1001Snack392030.221624105.4914714132.77
18-May-15High StreetNorth & CentralDelhiDelhi1001Dinner478326.51579755.511341499.65
18-May-15High StreetSouthHyderabadAndhra Pradesh1002Breakfast102919.2625772.72
18-May-15High StreetSouthHyderabadAndhra Pradesh1002Lunch218289.86412083.513318900.01
18-May-15High StreetSouthHyderabadAndhra Pradesh1002Snack328888.02542433.744254478.99
18-May-15High StreetSouthHyderabadAndhra Pradesh1002Dinner429440.27764147.15787352.55
18-May-15High StreetSouthHyderabadAndhra Pradesh1003Breakfast125325349177.05
18-May-15High StreetSouthHyderabadAndhra Pradesh1003Lunch232509.08511543.613947812.33
18-May-15High StreetSouthHyderabadAndhra Pradesh1003Snack339483.37671119.985237376.17
18-May-15High StreetSouthHyderabadAndhra Pradesh1003Dinner436658.14825789.636297839.63
18-May-15High StreetSouthHyderabadAndhra Pradesh1004Breakfast12141236.938800.36
18-May-15High StreetSouthHyderabadAndhra Pradesh1004Lunch216821.72255253.942122059.36
18-May-15High StreetSouthHyderabadAndhra Pradesh1004Snack319188.65367893.143004546.54
18-May-15High StreetSouthHyderabadAndhra Pradesh1004Dinner430749.51522403.793986165.71
18-May-15High StreetSouthHyderabadAndhra Pradesh1005Breakfast1299154615029.91
18-May-15High StreetSouthHyderabadAndhra Pradesh1005Lunch220776.37465879.183896834.88
18-May-15High StreetSouthHyderabadAndhra Pradesh1005Snack329085.13584998.164474962.43
18-May-15High StreetSouthHyderabadAndhra Pradesh1005Dinner439734.477349595464303.32
18-May-15IT ParkSouthBangaloreKarnataka1008Breakfast1021727646.5
18-May-15IT ParkSouthBangaloreKarnataka1008Lunch224701.35527201.14423561.8
18-May-15IT ParkSouthBangaloreKarnataka1008Snack318778.25406638.93081668.05
18-May-15IT ParkSouthBangaloreKarnataka1008Dinner421557.2491405.553949460.35
18-May-15MallNorth & CentralDelhiDelhi1009Breakfast10059
18-May-15MallNorth & CentralDelhiDelhi1009Lunch246292.8989138.367339227.45
18-May-15MallNorth & CentralDelhiDelhi1009Snack375595.361513032.7812696425.84
18-May-15MallNorth & CentralDelhiDelhi1009Dinner485261.881884253.6914383477.62
18-May-15High StreetSouthBangaloreKarnataka1010Breakfast10-312.57402.5
18-May-15High StreetSouthBangaloreKarnataka1010Lunch226716.2578170.254182295.3
18-May-15High StreetSouthBangaloreKarnataka1010Snack327725.2712407.924537838.22
18-May-15High StreetSouthBangaloreKarnataka1010Dinner434050.2795203.455621502.9
18-May-15Drive ThruSouthHyderabadAndhra Pradesh1012Breakfast105956.9335407.19
Valued Guide
Posts: 854

Re: managing the data for drilling up and down

When you say channel type do you mean DAYPARTID?  If so add a column for max(DAYPARTID).  Then the next step will be to calculate whatever you want based on max_DAYPARTID.

proc sql;

create table want as

select *,max(DAYPARTID) as max_DAYPARTID

from have

group by store;

data new;

set want;

if max_daypartid = 4 then do;

etc etc;



Posts: 50

Re: managing the data for drilling up and down

Hi Mark,

Thanks for the reply, the channel type has its own Channel ID which a breakup of sale for a day.

The challenge is, if we add it, the sale value comes wrong. Anyhow, I have to keep the store count correct so that there would no problem in drilling up to hierarchy.

Hierarchy: Date- Asset Type- Channel- Region- State- City- Store



Valued Guide
Posts: 854

Re: managing the data for drilling up and down

It still seems to me that it's a simple group by clause you are looking for but I still don't fully understand what it is you want.  Run this and let me know if this is it.  If not manually draw a column with what you are looking for so I can see an example:

data have;

infile cards dsd dlm=',';

informat date mmddyy10.;

format date mmddyy10.;

input Date assetType $ Region $ City $ State $ Store DAYPART $ DAYPARTID ADS_DAY ADS_MONTH ADS_YEAR;


5/18/2015,High Street,North & Central,Delhi,Delhi,1001,Breakfast,1,0,51427.12,377531.35

5/18/2015,High Street,North & Central,Delhi,Delhi,1001,Lunch,2,60754,1226183.81,9910780.29

5/18/2015,High Street,North & Central,Delhi,Delhi,1001,Snack,3,92030.22,1624105.49,14714132.77

5/18/2015,High Street,North & Central,Delhi,Delhi,1001,Dinner,4,78326.5,1579755.5,11341499.65

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1002,Breakfast,1,0,2919.26,25772.72

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1002,Lunch,2,18289.86,412083.51,3318900.01

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1002,Snack,3,28888.02,542433.74,4254478.99

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1002,Dinner,4,29440.27,764147.1,5787352.55

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1003,Breakfast,1,253,2534,9177.05

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1003,Lunch,2,32509.08,511543.61,3947812.33

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1003,Snack,3,39483.37,671119.98,5237376.17

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1003,Dinner,4,36658.14,825789.63,6297839.63

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1004,Breakfast,1,214,1236.93,8800.36

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1004,Lunch,2,16821.72,255253.94,2122059.36

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1004,Snack,3,19188.65,367893.14,3004546.54

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1004,Dinner,4,30749.51,522403.79,3986165.71

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1005,Breakfast,1,299,1546,15029.91

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1005,Lunch,2,20776.37,465879.18,3896834.88

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1005,Snack,3,29085.13,584998.16,4474962.43

5/18/2015,High Street,South,Hyderabad,Andhra Pradesh,1005,Dinner,4,39734.47,734959,5464303.32

5/18/2015,IT Park,South,Bangalore,Karnataka,1008,Breakfast,1,0,217,27646.5

5/18/2015,IT Park,South,Bangalore,Karnataka,1008,Lunch,2,24701.35,527201.1,4423561.8

5/18/2015,IT Park,South,Bangalore,Karnataka,1008,Snack,3,18778.25,406638.9,3081668.05

5/18/2015,IT Park,South,Bangalore,Karnataka,1008,Dinner,4,21557.2,491405.55,3949460.35

5/18/2015,Mall,North & Central,Delhi,Delhi,1009,Breakfast,1,0,0,59

5/18/2015,Mall,North & Central,Delhi,Delhi,1009,Lunch,2,46292.8,989138.36,7339227.45

5/18/2015,Mall,North & Central,Delhi,Delhi,1009,Snack,3,75595.36,1513032.78,12696425.84

5/18/2015,Mall,North & Central,Delhi,Delhi,1009,Dinner,4,85261.88,1884253.69,14383477.62

5/18/2015,High Street,South,Bangalore,Karnataka,1010,Breakfast,1,0,-312.5,7402.5

5/18/2015,High Street,South,Bangalore,Karnataka,1010,Lunch,2,26716.2,578170.25,4182295.3

5/18/2015,High Street,South,Bangalore,Karnataka,1010,Snack,3,27725.2,712407.92,4537838.22

5/18/2015,High Street,South,Bangalore,Karnataka,1010,Dinner,4,34050.2,795203.45,5621502.9

5/18/2015,Drive Thru,South,Hyderabad,Andhra Pradesh,1012,Breakfast,1,0,5956.93,35407.19



proc sql;

create table want as

select *,sum(ads_day) as day_sales

from have

group by date,store;

Posts: 50

Re: managing the data for drilling up and down

Hi Mark,

I am just trying to explain the problem once ogain....

I have the date mentioned above and want to add an additional category which channel but the problem is, the number of channels is not same for all the Stores and an average needs to be display on every drill up. However I managed it for Daypart which was same so I just devided by 4 to sale of entire day.

Valued Guide
Posts: 854

Re: managing the data for drilling up and down

What are you referring to as channel, give me an example of what you would see in the channel column.

Posts: 50

Re: managing the data for drilling up and down

Date            Region   State           City                     Store    Sale 

5/18/2015,   South,    Hyderabad,  Andhra Pradesh,  1002,    10,291

Here I have taken Sale for a Store and a day and this single entry has to be decided into several parts:

DAYPART- Breakfast




Channel-   Dine In

                Take Away



                Drive Through


these to categories has to be added in the above given sigle observation.

Dayparts are same for all stores which is 4 but Channels varies store to store. I managed Daypards  but not able to add Channel for the given data. please help.

Daypart is a breakup of day while Channel is a breakup for sale type.

Valued Guide
Posts: 854

Re: managing the data for drilling up and down

How do you infer the channel using the data that you have given?  Store 1002 and Sale 10,291.

I am guessing that you are leaving some very important information out.

Posts: 50

Re: managing the data for drilling up and down

which kind of information your are looking for.....

Posts: 53

Re: managing the data for drilling up and down

Can you show your calculations you use now?

Post a Question
Discussion Stats
  • 9 replies
  • 3 in conversation