DATA Step, Macro, Functions and more

Splitting a dataset using first. and last.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Splitting a dataset using first. and last.

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


Accepted Solutions
Solution
‎05-26-2016 03:20 PM
Trusted Advisor
Posts: 1,115

Re: Splitting a dataset using first. and last.

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.

View solution in original post


All Replies
Solution
‎05-26-2016 03:20 PM
Trusted Advisor
Posts: 1,115

Re: Splitting a dataset using first. and last.

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.

Occasional Contributor
Posts: 11

Re: Splitting a dataset using first. and last.

Thank you for the solution - and for explaining why!

 

Janet

Super User
Posts: 5,255

Re: Splitting a dataset using first. and last.

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.

Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 247 views
  • 2 likes
  • 3 in conversation