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
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;
Hello @GS2 Do you have a date/datetime variable to help us remerge?
@novinosrin Yes I have a study date variable that is on the first row
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;
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
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
;
?
Yes exactly moving the information from row 2 into row 1
Change this
if last.studyid then _temp=failure;
to
if _n_=2 then _temp=failure;
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;
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 !
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
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;
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.
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
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: