turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How to have obs with zero values when the data doe...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-20-2017 10:46 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to knveraraju91

05-20-2017 11:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to knveraraju91

05-20-2017 10:59 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to knveraraju91

05-23-2017 10:11 AM

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.