topic Converting Wide to long with two sets of variables in SAS Programming

Converting Wide to long with two sets of variables

BlueNose — Fri, 07 Sep 2018 15:58:05 GMT

Dear All,

I have a dataset with 4 dependent variables, 6 independent variables and 9 covariates.

For each combination of dependent and independent variable, I wish to run a model with the specific independent variables alongside the covariates (let's ignore the significance level issue for now). In order to avoid "copy+paste" of the same procedure 24 times, I was thinking to transpose my dataset to long format and use the by statement, I'll be more specific:

My data look like this:

And I want it like this:

So I can run a procedure such as GLM and do it "by DV, IV".

I know how to use PROC TRANSPOSE when I have one set of variables which I want to transpose from wide to long. But two...can't figure out how to do it. I wish to avoid the dirty work of doing set and where 24 times. Is there a way to do it ?

Any assistance will be most appreciated !

Thank you.

Re: Converting Wide to long with two sets of variables

Reeza — Fri, 07 Sep 2018 16:34:01 GMT

Since it's wide to long, two arrays within a data step is straightforward enough.

Are the indexes different though? That may be problematic to make it a long data set, or you'll have missing values for those observations.

data want;
set have;

array _dv(*) dv1-dv4;
array _iv(*) iv1-iv6;

do i=1 to dim(_dv);*will only go to 4!;
dv=_dv(i);
iv=_iv(i);
output;
end;

run;

run;

Re: Converting Wide to long with two sets of variables

BlueNose — Fri, 07 Sep 2018 19:39:57 GMT

Thank you !

Will it work if I have more IV's than DV's ? The loop runs until 4 only.

Re: Converting Wide to long with two sets of variables

Reeza — Fri, 07 Sep 2018 19:45:17 GMT

What do you want to happen to those rows? You can add a condition but you need to decide if you want to set them to missing, carry forward? The logic depends a bit on what you're doing in the next steps and need for those.

@BlueNose wrote:

Thank you !

Will it work if I have more IV's than DV's ? The loop runs until 4 only.