How to compare values within an ID

[Yiting]

In Enterprise Guide, if I have a table like this

REGIO	GESLACHT	VOORLETTERS	XXX_REGIO	XXX_GESLACHT	XXX_VOORLETTERS
1	A	1	R.H.M.	B	1	R.H.L.
2	A	2	D.A.	A	1	D.A.
3	B	1	A.	A	2	A.
4	A	1	S.B.M.J.	A	1	S.B.M.K.
5	A	2

I have sorted by GESLACHT (first) and REGIO (which is a time variable) (second) and then within each value of GESLACHT, I want to see if its VOORLETTERS is increasing or decreasing. I want an extra column to indicate this. If given a GESLACHt, its VOORLETTERS is always increasing, give all the rows with this GESLACHT 1 and otherwise, 0 in the extra column. How can I do this?

(Sorry I stole the table from another user, but the question is different).

[TomKari]

There's something contradictory in your question.

First you say, "I have sorted by GESLACHT (first) and VOORLETTERS (second)".

Then you say, "within each value of GESLACHT, I want to see if its VOORLETTERS is increasing or decreasing".

If you have sorted by VOORLETTERS, they will always be ascending, won't they?

Perhaps a different example would make it clearer to us.

   Tom

[Yiting]

You are very right! I have changed the variables sorted. Could you have a look at it again? Thanks!

[TomKari]

Makes a little more sense! To have something I could test, I used sashelp.cars, and substituted REGION, PRODUCT, and INVENTORY for GESLACHT, REGIO, and VOORLETTERS.

The PROC SORT at the start isn't part of the solution, it just gets the data in the right sequence.

This isn't exhaustively tested, so make sure it's actually doing what you want!

   Tom

proc sort data=sashelp.shoes out=work.Have(keep=REGION PRODUCT INVENTORY);
	by REGION PRODUCT;
run;

data Inter01;
	keep REGION PRODUCT AscendingFlag;
	retain OldINVENTORY AscendingFlag;
	set Have;
	by REGION PRODUCT;

	if first.PRODUCT then
		do;
			OldINVENTORY=.;
			AscendingFlag=1;
		end;

	if INVENTORY > OldINVENTORY then;
	else
		AscendingFlag=0;
	OldINVENTORY=INVENTORY;

	if last.PRODUCT then
		output;
run;

proc sql;
	create table Want as select h.*, f.AscendingFlag from Have h inner join 
		Inter01 f on(h.REGION=f.REGION and h.PRODUCT=f.PRODUCT);
quit;

[Yiting]

Hi, thank a lot! but I am still trying to understand OldINVENTORY variable. How was it defined? Why does it not appear in the Inter01 table?

[TomKari]

No problem!

I'm creating OldINVENTORY to hang on to the value of INVENTORY from the previous record. AscendingFlag is first set to 1, which is the "regularity" condition (all of the INVENTORY values are ascending within REGION and PRODUCT). Then, if an INVENTORY value is greater than the one in the record before, I set AscendingFlag to 0 (there is at least one case of the exception).

I restrict which variables appear in Inter01 with the "keep" statement. If you comment it out, you'll see the working values as they change, which might make things a little clearer.

   Tom

[Yiting]

Hi,

Thanks a lot. I probably have not expressed myself right... In your output, from line 3 to 4 and from line 4 to 5, Inventory is increasing, but AsendingFlag did not identify them as 1... 😉

[TomKari · Accepted solution]

It's important to note that Inter01 only has one line for the entire region/product grouping, indicating if ANY of the transitions between the previous and the current record wasn't ascending.

Here's a little different version of the program. It writes a line to Inter02 for every record, so you can see how AscendingFlag is set. It's important to note that it's a toggle; as soon as it gets set to zero, it stays that way for the rest of the grouping.

Tom

proc sort data=sashelp.shoes out=work.Have(keep=REGION PRODUCT INVENTORY);
	by REGION PRODUCT;
run;

data Inter01 Inter02;
	retain OldINVENTORY AscendingFlag;
	set Have;
	by REGION PRODUCT;

	if first.PRODUCT then
		do;
			OldINVENTORY=.;
			AscendingFlag=1;
		end;

	if INVENTORY > OldINVENTORY then;
	else AscendingFlag=0;
	OldINVENTORY=INVENTORY;

	if last.PRODUCT then
		output Inter01;
	output Inter02;
run;

proc sql;
	create table Want as select h.*, f.AscendingFlag from Have h inner join 
		Inter01 f on(h.REGION=f.REGION and h.PRODUCT=f.PRODUCT);
quit;

[Patrick]

@Yiting 

You need to sort by GESLACHT, VOORLETTERS, REGIO to guarantee that the sort order within a GESLACHT/VOORSLETTERS tuple remains in the required sequence for your analysis.

[Astounding]

Here's an approach. It's somewhat similar to the @TomKari suggestion, but uses your data and your variables.  First:

proc sort data=have;
   by geslacht regio;
run;

Then check for ascending values (top DO loop) and output the results (bottom DO loop).

data want;
   ascending=1;
   do until (last.geslacht);
      set have;
      by geslacht;
      if voorletters <= prior_voorletters then ascending=0;
      prior_voorletters = voorletters;
   end;
   do until (last.geslacht);
      set have;
      by geslacht;
      output;
   end;
run;

This logic assumes that equal values fail on the "ascending" test.  If you want to allow equal values as part of an ascending pattern, you can change the comparison ( <= would become < instead).