Help Regarding Coding

[deleted_user]

Hi,
I have a query related to SAS enterprise guide ,i need a sql
code to find out average as mentioned in below table. there are 2
variables Time Flag demand, so i need average of demand when
timeflag
=0-9 then when time flag = 10-18 then average of demand , when time
flag = 19-22 then average of demand and when time flag = 22-24 then
average of demand as shown in table below.so i need a code using CASE
in filter or code in Sql or data set.I need it urgently so please
help

Sample Data set


TIme Flag Demand Average
0 18.0 13.1
1 20.0
2 11.0
3 15.0
4 16.0
5 17.0
6 5.0
7 4.0
8 8.0
9 17.0 24.9
10 16.0
11 19.0
12 20.0
13 21.0
14 22.0
15 30.0
16 31.0
17 32.0
18 33.0 20.5
19 34.0
20 35.0
21 36.0
22 37.0 23.0
23 38.0
24 3 9.0


Thanks
Atul

[Cynthia_sas]

Hi:
You might consider posting this question in the SAS Enterprise Guide forum.

cynthia

[SAPPER]

HI Atul,
We can use the GROUPFORMAT option in the SET statement to subgroup the timeflag into ranges that are defined by us.

please refer:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000202968.htm#a002611573

example 4 for an example on GROUPFORMAT.

Here's the complete program:
I used the same data ...

/*Test Group Format option*/

OPTIONS FORMDLIM='#';
LIBNAME MYLIB 'C:\My SAS Files' ;
DATA MYLIB.Average1;
INPUT Timeflag 2. demand 4.1 ;
Datalines ;
0 18.0
1 20.0
2 11.0
3 15.0
4 16.0
5 17.0
6 5.0
7 4.0
8 8.0
9 17.0
10 16.0
11 19.0
12 20.0
13 21.0
14 22.0
15 30.0
16 31.0
17 32.0
18 33.0
19 34.0
20 35.0
21 36.0
22 37.0
23 38.0
24 39.0
;

/*Define the range of timeflag using a custom format*/

Proc format LIB = MYLIB;
value range
0-9 = '0-9'
10-18 ='10-18'
19-22 ='19-22'
23-24 ='23-24'
;
run;

/*Don't need to sort our data since our timeflag is already in order but just to generalize things i did a sort on timeflag */

proc sort Data = MYLIB.average1 out=sorted_average1;
by timeflag;
run;

Options FMTSEARCH = (MYLIB);
/*This option gives the path where SAS searches for custom formats*/

data temp;
Format timeflag range.; /*Assign our custom format range to timeflag variable*/
set sorted_average1;
by timeflag GROUPFORMAT;

/*Now the logic to calculate the sum and then the averages of each timeflag range*/

If FIRST.timeflag then do;
sum_demand=0;
count=0;
end;
sum_demand+demand;
count+1;
IF LAST.timeflag then do;
average_demand = sum_demand/(count);
end;
run;


proc print data =temp;
run;

[SAPPER]

Atul Here's the output ....Average of Demand for every range of timeflag....

########################################################################

The SAS System 15
20:09 Wednesday, February 10, 2010


Obs timeflag demand sum_demand count average_demand

1 0-9 18 18 1 .
2 0-9 20 38 2 .
3 0-9 11 49 3 .
4 0-9 15 64 4 .
5 0-9 16 80 5 .
6 0-9 17 97 6 .
7 0-9 5 102 7 .
8 0-9 4 106 8 .
9 0-9 8 114 9 .
10 0-9 17 131 10 13.1000
11 10-18 16 16 1 .
12 10-18 19 35 2 .
13 10-18 20 55 3 .
14 10-18 21 76 4 .
15 10-18 22 98 5 .
16 10-18 30 128 6 .
17 10-18 31 159 7 .
18 10-18 32 191 8 .
19 10-18 33 224 9 24.8889
20 19-22 34 34 1 .
21 19-22 35 69 2 .
22 19-22 36 105 3 .
23 19-22 37 142 4 35.5000
24 23-24 38 38 1 .

########################################################################

The SAS System 16
20:09 Wednesday, February 10, 2010

sum_ average_
Obs timeflag demand demand count demand

25 23-24 39 77 2 38.5000 Message was edited by: SAPPER

[SAPPER]

I tried my best to keep the program simple.
Hope you understood it....

THanks
SAPPER.

[deleted_user]

Thanks a lot sapper hope it will work...

[deleted_user]

If in case you need further simple way of doing this task using sql please see the code below:

Proc format ;
value range
0-9 = '0-9'
10-18 ='10-18'
19-22 ='19-22'
23-24 ='23-24'
;
run;

data check;
input Timeflag 2. demand 4.1 ;
new_var=put(Timeflag,range.);
cards ;
******* give your data input here ********;
******* copy the datalines in the same post here ********;
run;

proc sql;
select new_var, avg(demand) as demand_avg
from check
group by new_var;
quit;


You can mention the required variables in the select statement