Need help transposing longitudinal data (HRS) and creation of time variable

[sas_user124]

I have data with ID, repeated cognitive measure (cog1-cog6), and medication (SSRI) at start. I will eventually run the mixed procedure to determine the association between cognitive score at the time points and SSRI.

My code looks like:

**getting relevant variables;

data ucognition;
set analyze (keep=
HHIDPN SSRI 
cog1 cog2 cog3 cog4 cog5 cog6); 
run;


proc transpose data=ucognition out=longcog (rename =(col1=CogScore) rename = (_name_=cog));
by HHIDPN SSRI;
var cog1 cog2 cog3 cog4 cog5 cog6;
run;

proc print data=longcog (obs=10);
run;

 the transpose works as it should, and cog1-cog6 are stored in long format under cog

when i go to create a time variable, this is my code:

data longcog2;
set longcog;
cog=cog1; time=1; output;
cog=cog2; time=2; output;
cog=cog3; time=3; output;
cog=cog4; time=4; output;
cog=cog5; time=5; output;
cog=cog6; time=6; output;
keep HHIDPN SSRI time cog;
run;

proc print data=longcog2 (obs=10);
run;

However, now, SAS states that cog1-6 are all uninitialized, so the time doesn't get assigned to them.

Is there a reason it isnt being recognized? Everything is in numeric format. I am unsure if I should rename the cog variable? The initial data has it has r7cogtot = cog1, r8cogtot = cog2, etc.

I would like my final table to look like:

           SSRI     cog    cogscore

id1      yes       cog1       14

id1      yes       cog2       13

id1      yes      ...             13

id1      yes      ...              13

id1      yes      ...              ...

id1      yes       cog6       ...

id2     yes       cog1        25

id2     yes       ...             25

...        ...         ....              ....

if i code the transpose like this:

data ucognition;
set analyze (keep=
HHIDPN SSRI 
cog1 cog2 cog3 cog4 cog5 cog6); 
run;


proc transpose data=ucognition out=longcog (rename =(col1=CogScore) rename = (_name_=cog));
by HHIDPN SSRI **cog1 cog2 cog3 cog4 cog5 cog6**;
var cog1 cog2 cog3 cog4 cog5 cog6
    ;
run;

proc print data=longcog (obs=10);
run;

data longcog2;
set longcog;
cog=cog1; time=1; output;
cog=cog2; time=2; output;
cog=cog3; time=3; output;
cog=cog4; time=4; output;
cog=cog5; time=5; output;
cog=cog6; time=6; output;
keep HHIDPN SSRI time cog;
run;

proc print data=longcog2 (obs=10);
run;

 I get the right output and time works, but it repeats each person 6 times

like

id1                         SSRI               cog                 cogscore

id1

id1

... 36 times

id2

id2

...36 times

Any idea why this is happening?

[Patrick]

I've got some theory what could be happening but to really know and propose code that works you need to provide us with sample data and code that ideally allows us to replicate what you describe.

To create sample data you could use code similar to below. Please make sure that the sample data is representative for the by groups like is the combination of hhidpn and ssri the business key or not? 

And ideally the sample data and code you provide lets us replicate the issue you describe.

data ucognition;
  HHIDPN='A'; 
  SSRI='X';
  array cog{6} 8 (1,2,3,4,5,6);
  output;
  SSRI='Y';
  output;
run;

Also please post code that actually works in your environment. This bit you shared will create a syntax error.

And also try to provide consistent info. Like: Where does this unnamed column with ID<n> values suddenly come from?

[ballardw]

When you do this:

proc transpose data=ucognition out=longcog (rename =(col1=CogScore) rename = (_name_=cog));

the variable cog in the Longcog data set is character as _name_ is character.

Also the numeric values would likely be in Cogscore, not Cog1, Cog2, Cog3 as you transposed them into Cogscore.

So when you go to this step you have no variables to assign values to cog. If there were they would be attempting to go into character value.

data longcog2;
set longcog;
cog=cog1; time=1; output;
cog=cog2; time=2; output;
cog=cog3; time=3; output;
cog=cog4; time=4; output;
cog=cog5; time=5; output;
cog=cog6; time=6; output;
keep HHIDPN SSRI time cog;
run;

[Kurt_Bremser]

I guess you want this:

data longcog2;
set longcog;
time = input(substr(cog,4),best.);
run;