topic Re: make new variable from repeated measurements? in SAS Procedures

make new variable from repeated measurements?

loedrup_ab — Wed, 02 May 2012 07:57:32 GMT

Hi everybody

My dataset i organized like this spreadsheet. I have repeated measeurements for individuals with the ID 1-2 at time 1-6 and three dichotomous variables a-c. Variable B is only measured at time 1-3 and variable C is only measured at time 4-6. I want to make a variable with the number of 'yes' for variable B (example: V_t1-3) and a variable with the number of 'yes' for variable C (example:V_t4-6). I would prefer if these new variables were only listed once per ID, for example as I have done with V_t1-3 and V_t4_6 at time_1.

Any suggestions

I am a SAS novice using SAS 9.3

Sincerely

Anders

ID	Time	Variable A	Variable B	Variable C	V_t1-3	V_t4-6
1	1		no		0	2
1	2		no
1	3		no
1	4			yes
1	5			no
1	6			yes
2	1		yes		1	1
2	2		no
2	3		no
2	4			no
2	5			no
2	6			yes

Re: make new variable from repeated measurements?

Ksharp — Wed, 02 May 2012 08:18:03 GMT

How about:

Assuming every ID group has time 1.

data have;
input ID     Time     VariableA $     VariableB $     VariableC     $;
cards;
1     1     .     no     .
1     2     .     no     .          
1     3     .     no     .          
1     4     .     .     yes          
1     5     .     .     no          
1     6     .     .     yes          
2     1     .     yes      .
2     2     .     no     .          
2     3     .     no     .          
2     4     .     .     no          
2     5     .     .     no          
2     6     .     .     yes
;
run;
proc sql ;
create table want(drop=_:) as
 select *
  from have left join (select id as _id ,1 as _time ,sum(VariableB='yes') as V_t1_3,sum(VariableC='yes') as V_t4_6 from have group by id)
   on id=_id and time=_time;
quit;

Ksharp

Re: make new variable from repeated measurements?

loedrup_ab — Wed, 02 May 2012 08:44:28 GMT

Hi Ksharp

That was amazing. I worked perfectly. Thank you very much.

If you want to you, I would be very interested in learning the meaning of the different parts of your proc sql code. But only if you feel like it of course.

SIncerely

Anders

Re: make new variable from repeated measurements?

Ksharp — Wed, 02 May 2012 09:28:59 GMT

Of course.

(select id as _id ,1 as _time ,sum(VariableB='yes') as V_t1_3,sum(VariableC='yes') as V_t4_6 from have group by id)

this statement creat a table which contains V_t1_3 and V_t4_6 for each ID group ,then I use LEFT JOIN to bring it into the original dataset . That is it , EASY ??

Ksharp

Re: make new variable from repeated measurements?

loedrup_ab — Wed, 02 May 2012 09:32:55 GMT

Thanks again Ksahrp

You have been really helpfull.

Take care.

SIncerely

Anders

Re: make new variable from repeated measurements?

venkateshn — Wed, 02 May 2012 13:05:11 GMT

how we get v_t1_3 and v_t4_6 values here ...........

thanks

Re: make new variable from repeated measurements?

Linlin — Wed, 02 May 2012 13:23:11 GMT

Hi,

run the code below may help you to understand:

data have;
input ID     Time     VariableA $     VariableB $     VariableC     $;
cards;
1     1     .     no     .
1     2     .     no     .
1     3     .     no     .
1     4     .     .     yes
1     5     .     .     no
1     6     .     .     yes
2     1     .     yes      .
2     2     .     no     .
2     3     .     no     .
2     4     .     .     no
2     5     .     .     no
2     6     .     .     yes
;
run;

proc sql;
select id as _id ,1 as _time,sum(VariableB='yes') as V_t1_3,sum(VariableC='yes') as V_t4_6 from have group by id;
quit;