topic Re: Selecting observations of a variable in SAS Programming

Selecting observations of a variable

wj2 — Thu, 18 Nov 2021 21:20:48 GMT

Hello Community,

I need to create a dataset from a raw data file that only retains individuals in a study who completed a study assessment at two data points- one at study baseline (VISNO=01) AND one at study follow-up (VISNO=02). Individuals who only completed a study assessment at only one time point should not be included in the new dataset. I have provided an example of my input file as well as my desired output file below. Any help with how to program this would be greatly appreciated!

Have:

PID	VISNO	Other variables
01	01	…
01	02	…
02	01	…
03	01	…
03	02	…
04	02	…
05	02	…

Want:

PID	VISNO	Other variables
01	01	…
01	02	…
03	01	…
03	02	…

Re: Selecting observations of a variable

ballardw — Thu, 18 Nov 2021 22:30:38 GMT

If your data is sorted as shown this might work:

data want;
  set have;
  by pid ;
  if not (first.pid and last.pid);
run;

BY creates automatic boolean variables that indicate whether a record is the first or last of a by group. The variables are accessed using the First. and Last. (note the dots there) before the variable.

So if there is only a single record then it is both first and last at the same time.

@wj2 wrote:

Hello Community,

I need to create a dataset from a raw data file that only retains individuals in a study who completed a study assessment at two data points- one at study baseline (VISNO=01) AND one at study follow-up (VISNO=02). Individuals who only completed a study assessment at only one time point should not be included in the new dataset. I have provided an example of my input file as well as my desired output file below. Any help with how to program this would be greatly appreciated!

Have:

PID

VISNO

Other variables

01

01

…

01

02

…

02

01

…

03

01

…

03

02

…

04

02

…

05

02

…

Want:

PID

VISNO

Other variables

01

01

…

01

02

…

03

01

…

03

02

…

Re: Selecting observations of a variable

PaigeMiller — Thu, 18 Nov 2021 22:31:49 GMT

If you make VISNO numeric instead of character, this is pretty simple

proc sql;
    create table want as select * from have 
    where max(visno)=2 and min(visno)=1 
    group by pid;
quit;