managing the data for drilling up and down

[Rahul_SAS]

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.

Regards

Rahul

Date	assetType	Region	City	State	Store	DAYPART	DAYPARTID	ADS_DAY	ADS_MONTH	ADS_YEAR
18-May-15	High Street	North & Central	Delhi	Delhi	1001	Breakfast	1	0	51427.12	377531.35
18-May-15	High Street	North & Central	Delhi	Delhi	1001	Lunch	2	60754	1226183.81	9910780.29
18-May-15	High Street	North & Central	Delhi	Delhi	1001	Snack	3	92030.22	1624105.49	14714132.77
18-May-15	High Street	North & Central	Delhi	Delhi	1001	Dinner	4	78326.5	1579755.5	11341499.65
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1002	Breakfast	1	0	2919.26	25772.72
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1002	Lunch	2	18289.86	412083.51	3318900.01
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1002	Snack	3	28888.02	542433.74	4254478.99
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1002	Dinner	4	29440.27	764147.1	5787352.55
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1003	Breakfast	1	253	2534	9177.05
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1003	Lunch	2	32509.08	511543.61	3947812.33
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1003	Snack	3	39483.37	671119.98	5237376.17
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1003	Dinner	4	36658.14	825789.63	6297839.63
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1004	Breakfast	1	214	1236.93	8800.36
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1004	Lunch	2	16821.72	255253.94	2122059.36
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1004	Snack	3	19188.65	367893.14	3004546.54
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1004	Dinner	4	30749.51	522403.79	3986165.71
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1005	Breakfast	1	299	1546	15029.91
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1005	Lunch	2	20776.37	465879.18	3896834.88
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1005	Snack	3	29085.13	584998.16	4474962.43
18-May-15	High Street	South	Hyderabad	Andhra Pradesh	1005	Dinner	4	39734.47	734959	5464303.32
18-May-15	IT Park	South	Bangalore	Karnataka	1008	Breakfast	1	0	217	27646.5
18-May-15	IT Park	South	Bangalore	Karnataka	1008	Lunch	2	24701.35	527201.1	4423561.8
18-May-15	IT Park	South	Bangalore	Karnataka	1008	Snack	3	18778.25	406638.9	3081668.05
18-May-15	IT Park	South	Bangalore	Karnataka	1008	Dinner	4	21557.2	491405.55	3949460.35
18-May-15	Mall	North & Central	Delhi	Delhi	1009	Breakfast	1	0	0	59
18-May-15	Mall	North & Central	Delhi	Delhi	1009	Lunch	2	46292.8	989138.36	7339227.45
18-May-15	Mall	North & Central	Delhi	Delhi	1009	Snack	3	75595.36	1513032.78	12696425.84
18-May-15	Mall	North & Central	Delhi	Delhi	1009	Dinner	4	85261.88	1884253.69	14383477.62
18-May-15	High Street	South	Bangalore	Karnataka	1010	Breakfast	1	0	-312.5	7402.5
18-May-15	High Street	South	Bangalore	Karnataka	1010	Lunch	2	26716.2	578170.25	4182295.3
18-May-15	High Street	South	Bangalore	Karnataka	1010	Snack	3	27725.2	712407.92	4537838.22
18-May-15	High Street	South	Bangalore	Karnataka	1010	Dinner	4	34050.2	795203.45	5621502.9
18-May-15	Drive Thru	South	Hyderabad	Andhra Pradesh	1012	Breakfast	1	0	5956.93	35407.19

[Steelers_In_DC]

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;

end;

run;

[Rahul_SAS]

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

Regards

Anand

[Steelers_In_DC]

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;

cards;

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

;

run;

proc sql;

create table want as

select *,sum(ads_day) as day_sales

from have

group by date,store;

[Rahul_SAS]

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.

[Steelers_In_DC]

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

[Rahul_SAS]

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

                 Lunch

                 Snacks

                 Dinnner

Channel-   Dine In

                Take Away

                Corporate

                Delivery

                Drive Through

                ODC

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.

[Steelers_In_DC]

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.

[Rahul_SAS]

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

[Frank_Boekamp]

Can you show your calculations you use now?