BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jonate_H
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

7 REPLIES 7
FriedEgg
SAS Employee

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
Quartz | Level 8

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
SAS Employee

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
Quartz | Level 8

You're right. Thanks.

MY_
Calcite | Level 5 MY_
Calcite | Level 5

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
SAS Employee
@MY_ it would serve you best to create a new post specific to your question.
MY_
Calcite | Level 5 MY_
Calcite | Level 5

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

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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