How to set first observation to equal last observation

[GS2]

Hello,

Using SAS 9.4

I have long data where a patient with study id of 1 has 8 rows. In the last row is a populated variable 'failure' and I need to get the yes/no from the last study id to populate into the first study id record. The middle rows are not important just being able to get the information from the last row into the first row. Is their a method to do this in SAS? Thank you for the help!

Study ID      Failure

1                  .

1                  .

1                  . 

1                  Yes

[novinosrin]

Hello @GS2   Do you have a date/datetime variable to help us remerge?

[GS2]

@novinosrin Yes I have a study date variable that is on the first row

[novinosrin · Accepted solution]

data have;
input StudyID      Failure $;
cards;
1                  .
1                  .
1                  . 
1                  Yes
;

data want;
 do _n_=1 by 1 until(last.studyid);
  set have;
  by studyid;
  if last.studyid then _temp=failure;
 end;
 do _n_=1 to _n_;
  set have;
  by studyid;
  if _n_=1 then failure=_temp;
  output;
 end;
 drop _:;
run;

[GS2]

Is their a method to do this same type procedure but to move the information from the second row into the first row? Thank you

[novinosrin]

Do you mean , should the sample be

data have;
input StudyID      Failure $;
cards;
1                  .
1                  blah
1                  . 
1                  Yes
;

Requirement :

data want;
input StudyID      Failure $;
cards;
1                  blah
1                  blah
1                  . 
1                  Yes
;

 ?

[GS2]

Yes exactly moving the information from row 2 into row 1

[novinosrin]

Change this 

 if last.studyid then _temp=failure;

to

if _n_=2 then _temp=failure;

[GS2]

When I do this my variables changes "treatment A" or "Treatment B" to "yes" or "no" and it is not consistent with which treatment group is yes or no (meaning treatment group A is not always yes). Did I incorrectly apply the code? Thank you

data want;
do _n_=1 by 1 until(last.study_id);
set have;
by study_id;
if _n_=2 then _temp=rand_group;;
end;
do _n_=1 to _n_;
set haveS;
by study_id;
if _n_=1 then rand_group=_temp;
output;
end;
run;

[novinosrin]

Hmm This is the reason we need a good representative sample of

1. HAVE

2. WANT

3. Brief explanation of the logic. 

Take your time, get the requirement in full and let me know. It's SAS. So chill !

[GS2]

Using SAS 9.4

My data looks as follows

study_ID  DOB                   screening_date Rand_group

1              MMDDYY10.      MMDDYY10.     .

1              .                           .                         Group A

1              .                           .                         .

From this data I want to have the information from the Rand_group variable in row 2 moved into row 1 so I can run a frequency of this information along with other demographics that are in the data set on only the top row. Thank you

[novinosrin]

data have;
input studyID $ DOB   :mmddyy10. screening_date :mmddyy10. Rand_group & $;
format dob screening_date mmddyy10.;
cards;
1             06202000      06202018     .
1              .                           .                         Group A
1              .                           .                         .
;
/*If you know the value exists in the 2nd record of the by group, you can look up, copy and*/
/*plug in the 1st like below which is the same what we discussed earlier.*/
data want;
 do _n_=1 by 1 until(last.studyid);
  set have;
  by studyid;
  if _n_=2 then _temp=Rand_group;
 end;
 do _n_=1 to _n_;
  set have;
  by studyid;
  if _n_=1 then Rand_group=_temp;
  output;
 end;
 drop _:;
run;

/*If you do NOT know which record of the BY_GROUP has the non-missing value, you could search for*/
/*non missing, pick and plug in 1st like below*/

data want;
 do _n_=1 by 1 until(last.studyid);
  set have;
  by studyid;
  if not missing(Rand_group) then _temp=Rand_group;
 end;
 do _n_=1 to _n_;
  set have;
  by studyid;
  if _n_=1 then Rand_group=_temp;
  output;
 end;
 drop _:;
run;

[GS2]

I know the non-missing rand_group variable is always in the second row and so I copy and pasted the code and ran it. My results look as follows:

study_ID  DOB                   screening_date Rand_group

1              MMDDYY10.      MMDDYY10.     Yes

1              .                           .                         Group A

1              .                           .                         .

So instead of populating "Group A" I am getting a "Yes" or "No". The "Yes" however is not consistently only "Group A" or it would be fine. I am getting "Yes" for Group A and Group B and getting a "No" for Group A and Group B.

[novinosrin]

I can't see any YES or NO in your sample HAVE(Input)

Your sample is

data have;
input studyID $ DOB   :mmddyy10. screening_date :mmddyy10. Rand_group & $;
format dob screening_date mmddyy10.;
cards;
1             06202000      06202018     .
1              .                           .                         Group A
1              .                           .                         .
;

Are you sure you are testing against the correct input sample? 

Please verify what's in your input sample, and if you are testing against the sample and expecting the correct result

[GS2]

That is where my confusion is coming from because their is not a Yes/No anywhere in the rand_group column. I am using the same dataset in my 'set' statements so I am not sure what is happening. Thank you for your help though!