topic Re: Splitting a dataset using first. and last. in SAS Programming

Splitting a dataset using first. and last.

janet0102 — Thu, 26 May 2016 18:49:55 GMT

I've got a dataset with a maximum of 2 observations per ID (patid) and another variable that identifies the individual as being part of the study group (pain_id=1) or part of the comparison group (pain_id=0). Some individuals only have 1 observation and others have 2. I also have a variable with identifying the variable as the first or the second observation. I want to create a dataset containing individuals with only one observation and pain_id=1, a dataset containg only individuals with one observation and pain_id. Then I want a dataset for individuals having 2 observations.

Following is the syntax I'm using:

data pnpts nonpts twoobs;
   set allpts_count;
   by patid;
   if (first.patid and last.patid)and pain_id=1 then output pnpts;
   if (first.patid and last.patid) and pain_id=0 then output nonpts;
   else output twoobs;
run;

I'm able to create the 1st two datasets (pnpts and nonpts) correctly. However, the dataset twoobs contains both observations for individuals with 2 observations; but it also contains the individuals from the dataset pnpts even when they only have one observation. Twoobs does not contain the individuals from the dataset nonpts (who have only one observation).

Why am I getting the individuals from pnpts in the dataset twoobs - and more importantly, how do I prevent it?

Any help will be greatly appreciated!!

Thanks!

Janet

Re: Splitting a dataset using first. and last.

FreelanceReinh — Thu, 26 May 2016 18:57:25 GMT

Hi Janet,

Put an ELSE before the second IF and everything should be fine.

Without that, the existing ELSE statement refers only to the second IF, hence it includes the cases satisfying the first IF condition, but not the second.

Re: Splitting a dataset using first. and last.

LinusH — Thu, 26 May 2016 19:01:00 GMT

Please provide sample data in executable code (datalines) so we can test your code.

If you are using SAS Foundation, you could try the data step debugger that lets monitor the execution step by step.

Another option it to use put statements to monitor the progress of your program and variable values.

Re: Splitting a dataset using first. and last.

janet0102 — Thu, 26 May 2016 19:20:27 GMT

Thank you for the solution - and for explaining why!

Janet