topic Re: Trying to compare records in a data step in SAS Data Science

Trying to compare records in a data step

paul2877 — Fri, 09 Oct 2015 13:56:39 GMT

I have a table and I am trying to compare the the colors. Basically in the datastep... I want the primary color blue to count.. if the primary color blue isn't there I want to count the green... if no green then don't count. I am trying to understand how to write this so the observation looks at the next one

data new; set color_table;

if first.person and color='green' then;

count;

end;

Person	Color
Paul	green
Paul	blue
Paul	red
Paul	green
Paul	blue

end result

Person	Color	Count
Paul	blue	1
Paul	green	0
Paul	red	0
Paul	green	0
Paul	blue	1

Re: Trying to compare records in a data step

paul2877 — Fri, 09 Oct 2015 14:04:02 GMT

Trying to understand if there was a volume column there as well .. to sum up that column?

Re: Trying to compare records in a data step

RW9 — Fri, 09 Oct 2015 14:28:32 GMT

Sorry, I am really not following the logic. What do you mean by if blue is not there then count green? Both colors are in the data? If you mean sequentially, then you would need to attribute a distinct sequential order to the data.

Re: Trying to compare records in a data step

paul2877 — Fri, 09 Oct 2015 14:37:03 GMT

what I am trying to do is count one color or the other... For Paul.. I want to add 1 if the color is blue.. but if the next record is green.. don't count green..

Re: Trying to compare records in a data step

RW9 — Fri, 09 Oct 2015 14:58:47 GMT

So:

proc sql;
  create table WANT as
  select   distinct COLOR,
              count(COLOR) as NUM
  from     HAVE
  group by COLOR;
quit;

Re: Trying to compare records in a data step

paul2877 — Fri, 09 Oct 2015 15:01:02 GMT

This will give me a count by group.. however I only want to count green if blue isn't there.

Re: Trying to compare records in a data step

RW9 — Fri, 09 Oct 2015 15:04:04 GMT

Which then goes back to my previous question as in your test data, blue is clearly there?

Person	Color
Paul	green
Paul	blue
Paul	red
Paul	green
Paul	blue

Second and last row.

Re: Trying to compare records in a data step

paul2877 — Fri, 09 Oct 2015 15:09:14 GMT

Yep.. I know .. but I have a large data set.. and it won't always be the case. I need to look at each observation to see what the color is.. then if the first oberservation is blue then =1 .. if we go to the second oberservation it's green, how do I tell it to look at the previous observation to know that it was blue.

Re: Trying to compare records in a data step

RW9 — Fri, 09 Oct 2015 15:24:26 GMT

But what does observation order have to do with anything? If there is a record in the data for that particular group of blue, then there is a records in that particular group = blue, if its the first obs or the 5th doesn't matter. A mere matter of sorting the data appropriately will put the blue in the correct order, so I sort your dataset:

Paul Blue

Paul Green

Paul Red

There are Blue records?

Re: Trying to compare records in a data step

paul2877 — Fri, 09 Oct 2015 16:03:50 GMT

This is how I want the output to look at the previous obs to see if blue was in the list.. if it's not.. I want to have green have a count.. here is 2 scenarios
Person Color Count
Paul blue 1
Paul blue 1
Paul green 0
Paul green 0
Paul red 0

Person Color Count
Paul green 1
Paul green 1
Paul red 0