topic How do I create a new column with one variable from altenate rows containing duplicate information. in SAS Programming

How do I create a new column with one variable from altenate rows containing duplicate information.

akybono — Sun, 25 Sep 2016 17:30:48 GMT

There are 6000 more rows and a few more columns. As we can see in the image, exactly half the rows have duplicate information except for one box/variable (FEV1). We want to create a new column called FEV2 (in place of E3, E4, E5...) containing value of the one unique variable in every alternate row (D4, D6, D8...) and delete the ~3000 duplicate rows. Kindly help. I can also open the file in MS Excel, so excel procedures would also be welcome.

Re: How do I create a new column with one variable from altenate rows containing duplicate informati

Reeza — Sun, 25 Sep 2016 17:48:39 GMT

Please post data as text not image, we don't want to type out your data.

Also, show what you want as output based on your data

Re: How do I create a new column with one variable from altenate rows containing duplicate informati

Reeza — Sun, 25 Sep 2016 17:50:24 GMT

PS. I think you want a transpose, so look into proc transpose

Re: How do I create a new column with one variable from altenate rows containing duplicate informati

Astounding — Sun, 25 Sep 2016 20:46:26 GMT

Here's a (perhaps) simpler approach that doesn't require learning PROC TRANSPOSE:

data want;

set have (rename=(fev1=fev));

by mrn dos bmi;

fev2 = fev;

fev1 = lag(fev);

if last.bmi;

drop fev;

run;

It does require, however, that there are exactly two observations to be combined each time. If that's not the case, you can still use a more complex DATA step or else switch to PROC TRANSPOSE:

proc transpose data=have (rename=(fev1=fev)) prefix=fev out=want (drop=_name_);

var fev;

by mrn dos bmi;

run;

The programs are untested ... likely working as is but you may need to tweak them.