Creating one observation per subject between the first and last visit.

[michelle05]

Is there any information on how to create one observation per subject between the first and last visit in SAS data?

Thanks,

[PaigeMiller]

---

@michelle05 wrote:

Is there any information on how to create one observation per subject between the first and last visit in SAS data?

---

Much more information is needed. Specifically, we need to see a portion of your data AND an example of the desired output from this sample data.

[michelle05]

So I am to create a new data set (CHANGE) with one observation per subject with the difference in WEIGHT between the first and last visit.  I am also to include in this data set the number of days between the first and last visit.  Do not include any patients who have only one visit.

This is what I have done so far.

DATA CLINICAL;
*Use LENGTH statement to control the order of 
variables in the data set;
LENGTH PATIENT VISIT DATE_VISIT 8;
RETAIN DATE_VISIT WEIGHT;
DO PATIENT = 1 TO 25;
IF RANUNI(135) LT .5 THEN GENDER = 'FEMALE';
ELSE GENDER = 'MALE';
X = RANUNI (135);
IF X LT .33 THEN GROUP = 'A';
ELSE IF X LT .66 THEN GROUP = 'B';
ELSE GROUP = 'C';
DO VISIT = 1 TO INT (RANUNI(135)*5);
IF VISIT = 1 THEN DO;
DATE_VISIT = INT(RANUNI(135)*100)+15800;
WEIGHT = INT(RANNOR(135)*10 + 150);
END;
ELSE DO;
DATE_VISIT = DATE_VISIT + VISIT*(10 + INT(RANUNI(135)*50));
WEIGHT= WEIGHT + INT(RANNOR(135)*10);
END;
OUTPUT;
IF RANUNI(135) LT .2 THEN LEAVE;
END;
END;
DROP X;
FORMAT DATE_VISIT DATE9.;
RUN;
*Assume data set Clinical is already sorted by VISIT and DATE;
DATA DIFFERENCE;
SET CLINICAL;
LENGTH;
DIFF_WEIGHT= WEIGHT-LAG(WEIGHT);
IF NOT FIRST.VISIT THEN OUTPUT;
PROC PRINT DATA=DIFFERENCE;
RUN;
DATA CHANGE;
SET CLINICAL;
DIFF_WEIGHT=WEIGHT-LAG(WEIGHT);
***OMIT PATIENT WITH ONLY ONE VISIT;
IF FIRST.VISIT AND LAST.VISIT THEN DELETE;
***IF IT IS THE FIRST VISIT ASSIGN VALUES TO THE
RETAINED VARIABLES;

[PaigeMiller]

We need to see a portion of your data AND an example of the desired output from this sample data.

[michelle05]

Are you asking about after I run the code?  

[michelle05]

This is the portion that I entered in.

*Assume data set Clinical is already sorted by VISIT and DATE;
DATA DIFFERENCE;
SET CLINICAL;
LENGTH;
DIFF_WEIGHT= WEIGHT-LAG(WEIGHT);
IF NOT FIRST.VISIT THEN OUTPUT;
PROC PRINT DATA=DIFFERENCE;
RUN;
DATA CHANGE;
SET CLINICAL;
DIFF_WEIGHT=WEIGHT-LAG(WEIGHT);
***OMIT PATIENT WITH ONLY ONE VISIT;
IF FIRST.VISIT AND LAST.VISIT THEN DELETE;
***IF IT IS THE FIRST VISIT ASSIGN VALUES TO THE
RETAINED VARIABLES;

[michelle05]

NOTE: The data set WORK.CLINICAL has 49 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 703.00k

OS Memory 28840.00k

Timestamp 10/01/2019 01:02:18 AM

Step Count 59 Switch Count 2

Page Faults 0

Page Reclaims 164

Page Swaps 0

Voluntary Context Switches 10

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

99 *Assume data set Clinical is already sorted by VISIT and DATE;

100 DATA DIFFERENCE;

101 SET CLINICAL;

102 LENGTH;

103 DIFF_WEIGHT= WEIGHT-LAG(WEIGHT);

104 IF NOT FIRST.VISIT THEN OUTPUT;

NOTE: Variable 'FIRST.VISIT'n is uninitialized.

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

1 at 103:20

NOTE: There were 49 observations read from the data set WORK.CLINICAL.

NOTE: The data set WORK.DIFFERENCE has 49 observations and 7 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 949.28k

OS Memory 29100.00k

Timestamp 10/01/2019 01:02:18 AM

Step Count 60 Switch Count 2

Page Faults 0

Page Reclaims 129

Page Swaps 0

Voluntary Context Switches 11

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

105 PROC PRINT DATA=DIFFERENCE;

106 RUN;

NOTE: There were 49 observations read from the data set WORK.DIFFERENCE.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.09 seconds

user cpu time 0.09 seconds

system cpu time 0.00 seconds

memory 2329.18k

OS Memory 29864.00k

Timestamp 10/01/2019 01:02:18 AM

Step Count 61 Switch Count 0

Page Faults 0

Page Reclaims 211

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 32

107 DATA CHANGE;

108 SET CLINICAL;

109 DIFF_WEIGHT=WEIGHT-LAG(WEIGHT);

110 ***OMIT PATIENT WITH ONLY ONE VISIT;

111 IF FIRST.VISIT AND LAST.VISIT THEN DELETE;

112 ***IF IT IS THE FIRST VISIT ASSIGN VALUES TO THE

113 RETAINED VARIABLES;

114

115

116

117 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

128

[Patrick]

@michelle05 

It's really hard to know what's not working if we don't see the data. Ideally provide sample data in form of a SAS data step and then show us how the desired result should look like.

Looking into your code you've got the following Note:

NOTE: Variable 'FIRST.VISIT'n is uninitialized.

What this means: You forgot to add a BY VISIT DATE; to your data step. For this reason your IF FIRST.VISIT logic is not going to work.

Also: Please do me the favour and end a SAS Data Step with a RUN; statement; 

Below the code you've posted with the BY and RUN statements added.

*Assume data set Clinical is already sorted by VISIT and DATE;
DATA DIFFERENCE;
  SET CLINICAL;
  by visit date;
  LENGTH;
  DIFF_WEIGHT= WEIGHT-LAG(WEIGHT);

  IF NOT FIRST.VISIT THEN
    OUTPUT;
run;

PROC PRINT DATA=DIFFERENCE;
RUN;

DATA CHANGE;
  SET CLINICAL;
  by visit date;
  DIFF_WEIGHT=WEIGHT-LAG(WEIGHT);

  ***OMIT PATIENT WITH ONLY ONE VISIT;
  IF FIRST.VISIT AND LAST.VISIT THEN
    DELETE;

  ***IF IT IS THE FIRST VISIT ASSIGN VALUES TO THE
  RETAINED VARIABLES;
run;

[michelle05]

Yes sir!  I promise I am trying my best to learn the language and programming.  I get myself confused to the point where I'm always confused and doubting myself.  Thank you for the information.