topic Re: Create multiple observations from a single one in SAS Programming

Create multiple observations from a single one

sweettravel — Wed, 13 Oct 2010 04:11:39 GMT

Hi, all
I have the original data set like this:
Male Female V1 V2 V3 V4
3 2 2 4 5 7
2 3 6 9 3 8

Now I want to transform it into a new data set. Instead of male and female, now I have a new variable "gender", and gender=0 for male, gender=1 for female. If male=3, then repeat the observation 3 times with gender=0; if female=2, then repeat the observation 2 times with gender=1.

The transformed data set should look like:
Gender V1 V2 V3 V4
0 2 4 5 7
0 2 4 5 7
0 2 4 5 7
1 2 4 5 7
1 2 4 5 7
0 6 9 3 8
0 6 9 3 8
1 6 9 3 8
1 6 9 3 8
1 6 9 3 8

This task is really tricky for me. Wonder whether anyone have an idea of how to implement it. Thanks a lot!

Re: Create multiple observations from a single one

Rambo — Wed, 13 Oct 2010 04:34:26 GMT

Hi Sweettravel,

You can use the output statement [along with a do loop] to help print duplicate records.

Try using the following code:

data transformed;
set original;

gender = 0; /*sets the gender to male*/
do m = 1 to male;
output transformed;
end;
gender = 1; /* sets the gender to female*/
do f = 1 to female;
output transformed;
end;
keep gender v1-v4; /*ensures the key variables are kept*/
run;

I will note that the code above assumes male and female are both greater than 0. If this is not the case, you will need to add a conditional logic check before dropping into the DO loops.

Rambo

Re: Create multiple observations from a single one

sweettravel — Thu, 14 Oct 2010 04:10:24 GMT

Thanks a lot, Rambo. The code works great on my sample data, I will apply to my large data, too. Saves me lots of time, thanks again!