How to have obs with zero values when the data does not exsit

[knveraraju91]

Dear,

After running the below program the output i got is(Document attached). But I need to have in my outdata inaddition to the output is

eg;

for dose='100mg' and term='bleed' and sev='severe' there is only one obs in the output. I need to have obs with zero values for all SEV variable values for all OBS.

Please help in my code. Thank you

eg: output needed for a obs:

term    sev            50     100

bleed   mild           0         0

bleed   moderate  0         0

bleed   severe       0         1

output getting

term    sev            50     100

bleed   severe       0         1

data one;
input id dose $ term $ sev $;
datalines;
1 50mg hache mild
2 50mg naus moderate
3 100mg vomi mild
4 50mg ulce mild
5 100mg bleed severe
6 100mg rash moderate
7 50mg naus moderate
8 50mg naus mild
;
proc sql;
create table two as
select count(distinct ID) as NS,dose,term,sev
from one
group by dose,term,sev;
quit; 
proc sort data=two;
by dose term sev;
run;
proc transpose data=two out=three(drop=_NAME_);
  by dose term sev  ;
  var NS;
  id dose;
run;
data four;
  set three;
  array data  _50mg _100mg;
  do over data;
    if missing(data) then data=0;
  end;
  TOTAL= _50mg + _100mg ;
run;

[art297]

Your current method results in separate lines for each dose, thus the totals do not reflect the 50mg+100mg totals.

There may be a more efficient way, but I think that the following does what you want:

data one;
  input id dose $ term $ sev $;
  datalines;
1 50mg hache mild
2 50mg naus moderate
3 100mg vomi mild
4 50mg ulce mild
5 100mg bleed severe
6 100mg rash moderate
7 50mg naus moderate
8 50mg naus mild
;

proc sql noprint;
  create table doses as
  select distinct dose
      from one
  ;

  create table terms as
    select distinct term
      from one
  ;
  
  create table sevs as
    select distinct sev
      from one
  ;
  
  create table comb as
    select *, . as NS
      from doses,terms,sevs
        order by term,sev,dose
  ;
  
quit;


proc sql;
  create table two as
    select count(distinct ID) as NS,dose,term,sev
      from one
        group by term,sev,dose
  ;
quit;

data two;
  update two comb;
  by term sev dose;
run;

proc transpose data=two out=three(drop=_NAME_);
  by term sev;
  var NS;
  id dose;
run;

data four;
  set three;
  array data  _50mg _100mg;
  do over data;
    if missing(data) then data=0;
  end;
  TOTAL= _50mg + _100mg ;
run;

Art, CEO, AnalystFinder.com

[Patrick]

@knveraraju91

In case you're just after some sort of report then the following could do:

data one;
  input id dose $ term $ sev $;
  datalines;
1 50mg hache mild
2 50mg naus moderate
3 100mg vomi mild
4 50mg ulce mild
5 100mg bleed severe
6 100mg rash moderate
7 50mg naus moderate
8 50mg naus mild
;
run;

proc sql;
  create table combinations as
    select distinct t1.dose, t1.term, t2.sev
    from 
      one as t1, (select distinct sev from one) as t2
    order by t1.dose, t1.term, t2.sev
    ;
quit;

options missing='0';
proc tabulate data=one classdata=combinations;
  class dose term sev;
  table
    dose='Dose'*term='Term'*sev='Severity',
    n='Count'
    ;
run;

[s_lassen]

@knveraraju91:

If I understand you correctly, you want to have the output for all possible combinations of DOSE, TERM, and SEV, also those that have no observations.

One way to do this is to use PROC FREQ with the SPARSE options:

proc freq data=one;
  tables dose*term*sev/noprint sparse out=two(drop=percent);
run;

which will give you an exploded version of your TWO table, with all possible combinations.