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
- /
- SAS Procedures
- /
- Creating table with 5 rows even if all 5 aren't fi...

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

07-11-2011 05:15 PM

Hi all,

I have a macro that runs each ID in a group through a series of tests. One of the result tables of the test is a 5-row, 2-column table that shows the result of the test and the number of observations in this particular ID that meet each possible result. The table looks like this:

Result Count

A 15

B 2

C 33

D 45

E 11

All works fine when I try this on the bigger IDs, which have all five groups, but when I run it on the smaller IDs which may only have some of the results, I'm having trouble consistently outputting the same result. Ultimately, what I'd like to do is have the above table populated all the time with all five rows and have a zero value populated in the case that any given result has no observations:

What I want:

Result Count

A 15

B 0

C 33

D 45

E 11

What I'm getting:

Result Count

A 15

C 33

D 45

E 11

This is a common problem that I run into that I haven't quite figured out how to do consistently. I'm sure there are some pretty standard techniques to build a table template like what I'm looking for and then using some sort of conditional logic (or another approach) to fill in even when all five results aren't present.

Thanks!

Accepted Solutions

Solution

07-12-2011
04:38 AM

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

07-12-2011 04:38 AM

You can make a dataset temp like:

A

B

C

D

E

Then use code:

data want;

merge temp yourdataset;

by ...

run;

Ksharp

All Replies

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

07-11-2011 05:41 PM

You didn't show your current code, thus one can only guess. Two possibilities are using the sparse option in proc freq or the preloadfmt option in a number of procs. Take a look at:

http://www.nesug.org/proceedings/nesug02/cc/cc003.pdf

HTH,

Art

Solution

07-12-2011
04:38 AM

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

07-12-2011 04:38 AM

You can make a dataset temp like:

A

B

C

D

E

Then use code:

data want;

merge temp yourdataset;

by ...

run;

Ksharp

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

07-13-2011 09:36 AM

Thanks Ksharp - that was exactly the answer I needed! Nice and simple.