How to balance the "unbalanced data" and transpose the format

[Jonate_H]

I have unbalanced data: each ID in each year have different number of observations (in total 12 years and 50 different IDs).

I try to:

step 1, make each ID in each year have the same number of observations (6 obs/year), and set income as missing value for those missing months.

step 2, transpose the format of the "balanced" data set.

(for step 1, I tried to split data into small ones by id and by year, operate on each small data set, and then merge them back (the process is very inefficient); for step 2, I find something about "proc transpose" on the website is relevant, but haven't found the right way to do it)

[FriedEgg · Accepted solution]

You can perform both of these steps using PROC TRANSPOSE

proc transpose data=have out=want;

by year month;

var income;

id id;

run;

[Jonate_H]

That's pretty neat, thank you so much!

By the way, is there any easy way to do step 1? (I mean, balance the data but don't transpose the format)... Looks like I can re-transpose the data after running your code, am I right?

[FriedEgg]

You could transpose the data back again, yes.  You could write a datastep to do it, but I do not really see the benefit of having that intermediate data.

[Jonate_H]

You're right. Thanks.

[MY_]

Hi FriedEgg,

I am working on an analysis that needs me to get the intermediate data, that is, only conduct Step 1 in the original post. Do you know how I can do that?

Thank you!

[FriedEgg]

@MY_ it would serve you best to create a new post specific to your question.

[MY_]

Hi FriedEgg, I have sent SAS support an email and got the answers. Thank you for your kindly reply 🙂