topic Re: How to set first observation to equal last observation in SAS Programming

How to set first observation to equal last observation

GS2 — Fri, 23 Aug 2019 17:59:08 GMT

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 Yes

Re: How to set first observation to equal last observation

novinosrin — Fri, 23 Aug 2019 18:17:26 GMT

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

Re: How to set first observation to equal last observation

GS2 — Fri, 23 Aug 2019 18:18:41 GMT

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

Re: How to set first observation to equal last observation

novinosrin — Fri, 23 Aug 2019 18:31:07 GMT

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;

Re: How to set first observation to equal last observation

GS2 — Thu, 29 Aug 2019 19:17:27 GMT

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

Re: How to set first observation to equal last observation

novinosrin — Thu, 29 Aug 2019 19:19:34 GMT

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
;

Re: How to set first observation to equal last observation

GS2 — Thu, 29 Aug 2019 19:24:44 GMT

Yes exactly moving the information from row 2 into row 1

Re: How to set first observation to equal last observation

novinosrin — Thu, 29 Aug 2019 19:27:35 GMT

Change this

if last.studyid then _temp=failure;

if _n_=2 then _temp=failure;

Re: How to set first observation to equal last observation

GS2 — Thu, 29 Aug 2019 19:57:34 GMT

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;

Re: How to set first observation to equal last observation

novinosrin — Thu, 29 Aug 2019 20:01:44 GMT

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 !

Re: How to set first observation to equal last observation

GS2 — Fri, 30 Aug 2019 11:42:51 GMT

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

Re: How to set first observation to equal last observation

novinosrin — Fri, 30 Aug 2019 12:14:42 GMT


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;

Re: How to set first observation to equal last observation

GS2 — Fri, 30 Aug 2019 12:36:16 GMT

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.

Re: How to set first observation to equal last observation

novinosrin — Fri, 30 Aug 2019 12:41:55 GMT

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

Re: How to set first observation to equal last observation

GS2 — Fri, 30 Aug 2019 12:48:11 GMT

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!

Re: How to set first observation to equal last observation

novinosrin — Fri, 30 Aug 2019 12:55:07 GMT

You really need tor review your SET statement dataset calls

Play with some values to see distinct values


proc sql;

select distinct rand_group

from have;

quit;



/*or a */

proc freq data=have;

tables rand_group;

run;

The above will give you an idea what's in your dataset to eventually know what to expect

Re: How to set first observation to equal last observation

GS2 — Fri, 30 Aug 2019 13:02:31 GMT

This is the output from the proc freq of the data set I am using in the set statement

rand_group Frequency Percent Frequency Percent
Treatment A 27 50.94 27 50.94
Treatment B 26 49.06 53 100.00

Frequency Missing = 477

Re: How to set first observation to equal last observation

novinosrin — Fri, 30 Aug 2019 13:09:34 GMT

Ok so I suppose you got the same for

Proc sql;

select distinct rand_group

from have

quit;

as well.

In that case , you are definitely not referencing the correct dataset that picks yes/no from mars or moon lol

Re: How to set first observation to equal last observation

GS2 — Fri, 30 Aug 2019 13:17:54 GMT

Proc sql;

select distinct rand_group

from SPIES.SPIES_ANALYSIS_DEMOS

quit;

proc freq data = SPIES.SPIES_ANALYSIS_DEMOS;

table rand_group;

run;

Both show only treatment group a and b

data SPIES_ANALYSIS_DEMOS2;
do _n_=1 by 1 until(last.study_id);
set SPIES.SPIES_ANALYSIS_DEMOS;
by study_id;
if _n_=2 then _temp=Rand_group;
end;
do _n_=1 to _n_;
set SPIES.SPIES_ANALYSIS_DEMOS;
by study_id;
if _n_=1 then Rand_group=_temp;
output;
end;
drop _:;
run;

I am setting it to the same data set that is showing only treatment a and b

Re: How to set first observation to equal last observation

novinosrin — Fri, 30 Aug 2019 13:25:50 GMT

And is it still show YES, NO?

If it does, can you check upstream thoroughly. And if it is not confidential that would violate security/compliance, can you attach your sas dataset here? So I can work on it