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 ... View more

I think you could do it if you wanted a text file output (just replace the comma with the ascii crlf (which i think is '0a0d'x).  I don't think sas datasets work like that as you need to output a new row instead of just replacing the text. ... View more

Thanks pretty neat..expanding on that for this specifics: This code: data test; input       q1 q2 q3 q4 q5 q6 q7 q8 q9 q10       q11 q12 q13 q14 q15 q16 q17 q18 q19 q20       q21 q22 q23 q24 q25 q26 q27 q28 q29 q30       q31 q32 q33 q34 q35 q36 q37 q38 q39 q40       q41 q42 q43 q44 q45 q46 q47 q48; cards; 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 ; run; data want (keep=q x:);   array _q(*) q1-q48;   array _x1(48);   array _x2(48);   array _x3(48);   array _x4(48);   set test end=last;   retain _x1: _x2: _x3: _x4: ;   do i=1 to 48;     _x1(i)+(_q(i) eq 1);     _x2(i)+(_q(i) eq 2);     _x3(i)+(_q(i) eq 3);       _x4(i)+(_q(i) eq 4);     if i eq 1 then _xtotal+1;     if last then do;       q=vname(_q(i));       x1=_x1(i)/_xtotal;       x2=_x2(i)/_xtotal;       x3=_x3(i)/_xtotal;         x4=_x4(i)/_xtotal;       output;     end;   end; run; produces this output: q1 0.4 0.2 0.2 0.2 q2 0.2 0.4 0.2 0.2 q3 0.2 0.2 0.4 0.2 q4 0.2 0.2 0.2 0.4 q5 0.4 0.2 0.2 0.2 q6 0.2 0.4 0.2 0.2 q7 0.2 0.2 0.4 0.2 q8 0.2 0.2 0.2 0.4 q9 0.4 0.2 0.2 0.2 q10 0.2 0.4 0.2 0.2 q11 0.2 0.2 0.4 0.2 q12 0.2 0.2 0.2 0.4 q13 0.4 0.2 0.2 0.2 q14 0.2 0.4 0.2 0.2 q15 0.2 0.2 0.4 0.2 q16 0.2 0.2 0.2 0.4 q17 0.4 0.2 0.2 0.2 q18 0.2 0.4 0.2 0.2 q19 0.2 0.2 0.4 0.2 q20 0.2 0.2 0.2 0.4 q21 0.4 0.2 0.2 0.2 q22 0.2 0.4 0.2 0.2 q23 0.2 0.2 0.4 0.2 q24 0.2 0.2 0.2 0.4 q25 0.4 0.2 0.2 0.2 q26 0.2 0.4 0.2 0.2 q27 0.2 0.2 0.4 0.2 q28 0.2 0.2 0.2 0.4 q29 0.4 0.2 0.2 0.2 q30 0.2 0.4 0.2 0.2 q31 0.2 0.2 0.4 0.2 q32 0.2 0.2 0.2 0.4 q33 0.4 0.2 0.2 0.2 q34 0.2 0.4 0.2 0.2 q35 0.2 0.2 0.4 0.2 q36 0.2 0.2 0.2 0.4 q37 0.4 0.2 0.2 0.2 q38 0.2 0.4 0.2 0.2 q39 0.2 0.2 0.4 0.2 q40 0.2 0.2 0.2 0.4 q41 0.4 0.2 0.2 0.2 q42 0.2 0.4 0.2 0.2 q43 0.2 0.2 0.4 0.2 q44 0.2 0.2 0.2 0.4 q45 0.4 0.2 0.2 0.2 q46 0.2 0.4 0.2 0.2 q47 0.2 0.2 0.4 0.2 q48 0.2 0.2 0.2 0.4 ... View more

I don't understand what your data look like.  Can you give a quick example? ... View more

This code: data test; input q $ ans; cards; q1 1 q1 2 q1 3 q1 4 q1 1 q2 1 q2 2 q3 1 ; run; proc sql; create table summary as select       q,       sum(case when ans=1 then responses else 0 end)/count(*) as Pct_1,       sum(case when ans=2 then responses else 0 end)/count(*) as Pct_2,       sum(case when ans=3 then responses else 0 end)/count(*) as Pct_3,       sum(case when ans=4 then responses else 0 end)/count(*) as Pct_4 from       (select q, ans, count(*) as Responses from work.test group by q, ans) group by q; select * from summary; quit; produces this output: q Pct_1 Pct_2 Pct_3 Pct_4 q1 0.5 0.25 0.25 0.25 q2 0.5 0.5 0 0 q3 1 0 0 0 ... View more

I think the normal SAS average function excludes null values from the denominator as well.  So, your calculation would end up being divided by 3 instead of 5.  If that's what you want, then you could use it.  If not, then you'll have to calculate it as sum(shr*opt)/count(*) which would equate to 3.5*8.9.5 ... View more

I think you're right.  The standard average function doesn't calculate the same way the OP's formula does.  To accomplish that, I think he would have to change my formula from avg(shr*opt) to sum(shr*opt)/count(*). ... View more

I believe this code is pretty straightforward: data work.detail_data; input       Year       shr       opt; datalines; 1992 2.4 6 1992 2.3 . 1992 . 86 1993 . 6.5 1993 2.3 0 1993 2.6 0 1993 3.5 8.9 ; run; proc sql; select       year,       avg(shr*opt) as NewVar from work.detail_data group by year; quit; It produces this output: Year NewVar 1992 14.4 1993 10.38333 ... View more

I think a straightforward sql approach would be easier to understand: data work.detail_data; input Dwelling_ID Person_ID ID $ Mom_ID Dad_ID; datalines; 1001 101 1001_101 . . 1001 102 1001_102 . . 1001 103 1001_103 . . 1001 104 1001_104 101 . 1001 105 1001_105 . 102 ; run; proc sql; select t1.dwelling_id, t1.person_id, t1.id, t1.Mom_ID, t1.Dad_ID, case when count(t2.person_id)>0 then 1 else 0 end as IsMom, case when count(t3.person_id)>0 then 1 else 0 end as IsDad from work.detail_data t1 left outer join work.detail_data t2   on t1.Person_ID=t2.Mom_id left outer join work.detail_data t3   on t1.Person_ID=t3.Dad_ID group by t1.dwelling_id, t1.person_id, t1.id, t1.mom_id; quit; produces this output Dwelling_ID Person_ID ID Mom_ID Dad_ID IsMom IsDad 1001 101 1001_101 . . 1 0 1001 102 1001_102 . . 0 1 1001 103 1001_103 . . 0 0 1001 104 1001_104 101 . 0 0 1001 105 1001_105 . 102 0 0 ... View more