CODEWITHSUNDEEP
sqlsasformat
Alex F
Alex F

Reputation: 2274

How to insert uneven data rows into matrix in SAS?

I have an originations data set with loan ids. I then have a corresponding dataset with performance data for each of these loans ids, which can be anywhere from 10-40 rows in the performance data set.

The start date of each of the performance loans is not the same either, although some do overlap. What I want to do is take every loan id group in the performance data set, and then create a row of a certain column value across all occurrences in the data set. It doesn't matter if they start on different dates, I just want to align the values as this is the first value for loan id x and y.

For example:

ID Date Val

3 201601 100

3 201602 102

3 201603 103

--> Result:

ID Val1 Val2 Val3

3 100 102 103

I'm having two issues. One is the differing size of performance data for each id. I can't construct a matrix with differing lengths of rows. I'm assuming I'll need to append 0's to the end of each row to meet a predefined width.

My second issue is that I'm not sure how to read through a the performance data set to group loans, extract the value column, construct the column into a row for that id, and then insert into a matrix. I know how I would do this in Python but I need to use SAS. I can construct tables in SAS, but I'm not sure how to append rows, only columns.

If someone could provide some guidance on this it'd be a great help.

Upvotes: 0

Views: 118

Answers (1)

Alex F
Alex F

Reputation: 2274

Anyone who runs into a similar issue it ended up being only a few lines of code.

proc transpose  data =  new_data
            out =  new_data1;
            var trans_state;
            by id;
run;

The output will be

enter image description here

Upvotes: 0

Related Questions