BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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;
3 REPLIES 3
art297
Opal | Level 21

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
Opal | Level 21

@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;

Capture.PNG

s_lassen
Meteorite | Level 14

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 848 views
  • 3 likes
  • 4 in conversation