How do I create thus new column and variable?

[DOYLE1]

Hi Everyone,

I need some help with a SAS problem. I am doing some research into twinning and need to identify a way of calculating the sex ratio of twins. What I want is to create a new column which will say FF for female/female twins, MM for male/male twins and MF for male/female twins. My data looks like this:

Mother Father d.o.b idtwin twin/single sex
123 987 15/9/99 12398715999 1 m
345 678 1/2/99 3456781299 2 m
345 678 1/2/99 3456781299 2 m
456 123 2/5/99 4561232599 1 f
674 789 3/4/99 6747893499 1 m
345 567 8/9/99 3455678999 2 m
345 567 8/9/99 3455678999 2 f

etc. Where idtwin is made by compressing the mother, father & date of birth and teh twin/single column is either 1=single or twin=twin.

I'd really appriciate any help on how to create a column and get a variable for twins to be eitehr ff, mm or mf.

Thanks!

 

[jj72uk]

Hi,

First off I created a CSV file of the above data for quickness and named the dataset xdates, you'd obviously point xdates to the dataset name which has the data which you stated in your question..

What I'd do is create a helper column based on there sex, then summaries the data before merging it back into the orginal data thus giving you your extra var.

See below:

data xxdates;
set xdates;
if single = 'm' then val = 1;
if single = 'f' then val = 3;
run;
proc summary data = xxdates nway;
class mother father dob idtwin;
var val;
output out = xxdatesumed (drop = _type_ _freq_)sum=;
run;
data twingrp;
set xxdatesumed;
length group $2;
if val = 1 then group = 'M';
if val = 3 then group = 'F';
if val = 2 then group = 'MM';
if val = 4 then group = 'MF';
if val = 6 then group = 'FF';
run;
proc sort data = twingrp;
by mother father dob idtwin;
run;
proc sort data = xdates;
by mother father dob idtwin;
run;
data groupmerge (drop = val);
merge xdates (in=a) twingrp (in=b);
by mother father dob idtwin;
if a;
run;

This should work.

HTH

 

[MatthiasB]

Alternatively you could work with RETAIN. Assuming your input table is WORK.kids (code not tested):

[TT]/* get twins into sequence, female before male */
proc sort
data=WORK.kids;
by idTwin sex;
run;

/* get groups gender */
data WORK.twinGender (keep=idTwin twinGender);
set WORK.kids (keep=idTwin sex twinSingle
where=twinSingle ne 1);
by idTwin;
length twinGender $2;
retain twinGender;
/* build twin gender */
if first.idTwin then
twinGender = left(sex);
else do;
twinGender = strip(twinGender) !! left (sex);
output;
end;
run;

/* get twinGenders onto the kids table, input already sorted */
data WORK.kids;
merge WORK.kids
WORK.twinGender (in=fromTwin);
by idTwin;
if not fromTwin then
twinGender = left(sex);
run;[/TT]

Cheers,
Matthias