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 |
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
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
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
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
Thanks again Ksahrp
You have been really helpfull.
Take care.
SIncerely
Anders
hi
how we get v_t1_3 and v_t4_6 values here ...........
thanks
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.