DATA Step, Macro, Functions and more

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

Reply
Occasional Learner
Posts: 1

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

sas.JPG

 

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.

Super User
Posts: 19,878

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

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

Super User
Posts: 19,878

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

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

Super User
Posts: 5,518

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

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.

Ask a Question
Discussion stats
  • 3 replies
  • 270 views
  • 0 likes
  • 3 in conversation