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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 130
Accepted Solution

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

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)

unbalanced.png

Attachment

Accepted Solutions
Solution
‎05-24-2014 11:57 AM
Trusted Advisor
Posts: 1,300

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

You can perform both of these steps using PROC TRANSPOSE

proc transpose data=have out=want;

by year month;

var income;

id id;

run;

View solution in original post


All Replies
Solution
‎05-24-2014 11:57 AM
Trusted Advisor
Posts: 1,300

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

You can perform both of these steps using PROC TRANSPOSE

proc transpose data=have out=want;

by year month;

var income;

id id;

run;

Frequent Contributor
Posts: 130

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

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?

Trusted Advisor
Posts: 1,300

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

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.

Frequent Contributor
Posts: 130

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

You're right. Thanks.

New Contributor MY_
New Contributor
Posts: 2

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

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!

 

 

Trusted Advisor
Posts: 1,300

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

@MY_ it would serve you best to create a new post specific to your question.
New Contributor MY_
New Contributor
Posts: 2

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

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

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1275 views
  • 1 like
  • 3 in conversation