topic Re: Need help transposing longitudinal data (HRS) and creation of time variable in SAS Programming

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

sas_user124 — Tue, 30 Jan 2024 01:22:21 GMT

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 ... ...

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

id1 SSRI cog cogscore

id1

... 36 times

id2

...36 times

Any idea why this is happening?

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

Patrick — Tue, 30 Jan 2024 11:56:59 GMT

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?

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

ballardw — Tue, 30 Jan 2024 13:39:32 GMT

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;

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

Kurt_Bremser — Tue, 30 Jan 2024 15:04:54 GMT

I guess you want this:

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