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
- /
- create design matrix from long dataset

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
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-20-2017 12:15 PM

I have a long dataset like the example below (in my real dataset I have thousands of ID's and approx one thousand distinct Dx values).

ID Dx

Pat1 A

Pat1 B

Pat1 C

Pat2 B

Pat2 D

Pat3 A

Pat3 D

And I want to generate a design matrix like the one below (obviously the real design matrix will be high dimensional with a column for each Dx value).

ID A B C D

Pat1 1 1 1 0

Pat2 0 1 0 1

Pat3 1 0 0 1

My original plan was to transpose the long dataset, and then let proc glm generate the design matrix using a class statement; however I can't get the data to transpose how proc glm would need it with distinct dx values in each dx column.

Using transpose I haven't been able to get anything better than the structure below.

ID Dx1 Dx2 Dx3

Pat1 A B C

Pat2 B D

Pat3 A

I've thought about approaches using loops, arrays, and proc sql, but I haven't been able to put the pieces together to get to the end design matrix I need.

Any help is appreciated, and while any answer that works is great, in my situation a proc sql solution would be optimal.

Thanks much!

Accepted Solutions

Solution

09-21-2017
09:55 AM

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

Posted in reply to bootstrap_armada

09-20-2017 12:20 PM - edited 09-20-2017 12:22 PM

PROC TRANSPOSE can handle this. You just need to use this statement:

id Dx;

Since Dx is no longer going to be in the VAR statement, you may need to take a couple of additional steps (try it and see if they are needed):

Before transposing, create a variable that is 1 on each observation, and use that in the VAR statement of PROC TRANSPOSE.

After the transpose go back and replace missing values with zeros.

One final consideration ... your incoming data set may may be to deduplicated. You can't have multiple observations for the same ID with the same DX.

All Replies

Solution

09-21-2017
09:55 AM

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

Posted in reply to bootstrap_armada

09-20-2017 12:20 PM - edited 09-20-2017 12:22 PM

PROC TRANSPOSE can handle this. You just need to use this statement:

id Dx;

Since Dx is no longer going to be in the VAR statement, you may need to take a couple of additional steps (try it and see if they are needed):

Before transposing, create a variable that is 1 on each observation, and use that in the VAR statement of PROC TRANSPOSE.

After the transpose go back and replace missing values with zeros.

One final consideration ... your incoming data set may may be to deduplicated. You can't have multiple observations for the same ID with the same DX.

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

Posted in reply to Astounding

09-21-2017 09:56 AM

This is a good, simple solution - I hadn't thought of that approach with proc transpose. Thanks!

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

Posted in reply to bootstrap_armada

09-20-2017 12:29 PM

Hi,

Or if just need to generate a matrix.

proc tabulate data=have;

class id dx;

table id,dx=""*N="";

run;