sql with group by

[UrvishShah]

Try this one...

%macro test;

            proc sql;

                  select count(distinct fee) into :fees_group

                  from test;

            quit;

            proc sql;

                  create table test as

                  select calendar_day,order_status,fee,sum(no) as total

                  from total

                  group by 1,2,3;

            quit;

            %if %eval(&fees_group. EQ 0) %then %do;

                   proc sql;

                          insert into test

                          set calendar_day = 1/8/2013,

                                order_status = "start",

                                fee = "Free",

                                total = .;

                    quit;

             %end;

%mend test;

%test;

[DBailey]

If you create two static data sets that have all of the values you want:

data work.statuses;

order_status='Start';

output;

order_status='End';

output;

run;

data work.fees;

fee='paid';

output;

fee='free';

output;

run;

Then you can use proc sql to get all of the possible combinations:

proc sql;

select

    m.calendar_day

    ,m.order_status

    ,m.fee

    ,sum(coalesce(h.no,0))as  Total_No

from (

    select distinct    t1.calendar_day, t2.order_status,t3.fee

    from work.have t1, work.statuses t2, work.fees t3) m

    left outer join work.have h

        on m.calendar_day=h.calendar_day

           and m.order_status=h.order_status

           and m.fee=h.fee

group by

    m.calendar_day

    ,m.order_status

    ,m.fee;

quit;

produces

08JAN2013	End	free	0
08JAN2013	End	paid	0
08JAN2013	Start	free	4
08JAN2013	Start	paid	41

[Reeza]

If you're not stuck on the format you could do something like the following:

proc sql;

create table want as

Select calendar_day, order_status, fee, sum((fee='paid')*no) as total_paid, sum((fee='free')*no) as total_free

from have

group by calendar_day, order_status;

quit;

[Tal]

so the  previous code you sent with the sparse  function works  perfectly Reeza,nice short and  easy.No need to insert any additional records  since the "free" appeares at least once within the week i am supposed to analyze .

So thanks again

Thanks to you all guys for your fancy codes as well