DATA Step, Macro, Functions and more

Retain increment

Reply
Contributor
Posts: 21

Retain increment

Hi, I posted because of a problem with a title in export (PDF) (Which has been solved) I have now an other problem :

I would like to create a table like this one :

 

Player   Rank

P1           1

P2           1

P3           1

P4           4

 

I tried this :

DATA table;
SET RESULTAT (FIRSTOBS=2);
RETAIN rank 1;
RETAIN j;
retain i;
ATTRIB
	Rang
		LABEL="Rank";

	i=0;
	IF gold=lag(gold) AND silver=lag(silver) AND bronze=lag(bronze) THEN j=j+1 AND rang=rang AND i=1;
	
	IF gold ne lag(gold) AND silver ne lag(silver) AND bronze ne lag(bronze) AND i=1 THEN rank=rank+j;
	ELSE rank=rank+1 AND i=0;
RUN;

And Instead of, I just have :

 

Player   Rank

P1           1

P2           1

P3           1

P4           2

 

Also, how could I do without the lag function ?

Thank you.

Super User
Super User
Posts: 9,407

Re: Retain increment

Without seeing some data it quite hard to say, and please don't go posting screenshots or files or something else.  You can see in other posts how people have posted a small amount of test data to illustrate a point, using a datastep so it can be run straight off.  Use this post if need be:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

 

Without data I can't really alter your code.

 

You may be able to something with the rank procedure, but it looks like your conditional logic is too complicated, probably easier to use a datastep.  

 

Also, as per previous posts, please avoid coding all in uppercase - it sounds like your shouting at us!

Super User
Posts: 13,304

Re: Retain increment

You would have to show us what your RESULTAT data set looks like.

If you attempt to RETAIN a variable with the same name as one in your input data set each time an observation is read from the input set it will reset the value to that in the input set.

 

Second, LAG has a separate queue for each variable and use. Conditional statements involving LAG almost never work as you think they should. You would be better off to create unconditionally the lagged value and use those such as:

 

lg=lag(gold);

ls=lag(silver);

lb=lag(bronze);

if gold=lg and silver=ls and bronze=lb then <whatever>.

 

and then drop the lagged values from the data set after verifying the code behaves as desired.

drop lg ls lb;

Super User
Posts: 6,629

Re: Retain increment

When using an IF/THEN statement, there can be only one result.  This is incorrect syntax:

 

... THEN j=j+1 AND rang=rang AND i=1;

 

SAS can interpret it, but the result is not what you are intending.

 

Instead, you have to list the results as separate statements inside a DO group:

 

... THEN DO;

   j=j+1;

   rang=rang;

   i=1;

END;

 

There must be an END to match up with DO.

Contributor
Posts: 21

Re: Retain increment

I can't find the autoexec file.

Thanks Astounding but I have the same result, as this :

 

Player   Rank

P1           1

P2           1

P3           1

P4           2

Super User
Posts: 13,304

Re: Retain increment

[ Edited ]

Num19 wrote:

I can't find the autoexec file.

Thanks Astounding but I have the same result, as this :

 

Player   Rank

P1           1

P2           1

P3           1

P4           2


What does an autoexec file have to do with this?

 

Show the exact code you run and the log. Post it into a code box opened with the forum {I} menu icon.

If your code has an error then the previous result is not replaced. Since your code shows use of variables I and j and you do not show any code dropping them,  your "result" is incomplete because it does not include them.

Contributor
Posts: 21

Re: Retain increment

[ Edited ]

I precise it because I can't export datas.

Here is an example of what I have : example.PNG

 

I would like to have 3 and not 2 as in the picture.

Sorry for the pic RW9.

Trusted Advisor
Posts: 1,309

Re: Retain increment

You apparently have the data in order from the highest rank (the best) to the lowest.  So the first record has rank=1.   And I think you want all instances of ties in gold and silver and bronze to be assigned identical ranks - namely the highest rank for which they are tied.

 

Then, taking advent of the order the dataset already has, you can

 

data want;

  set have;

  by gold silver bronze notsorted;

  retain rank;

  if first.bronze then rank=_n_;

run;

 

Do I understand your objective correctly?

Contributor
Posts: 21

Re: Retain increment

Not really, the two bad points in this code :

DATA table;
SET RESULTAT (FIRSTOBS=2);
RETAIN rank 1;
RETAIN j;
retain i;
ATTRIB
	Rang
		LABEL="Rank";

	i=0;
	IF gold=lag(gold) AND silver=lag(silver) AND bronze=lag(bronze) THEN rang=rang;
	ELSE rank=rank+1 AND i=0;

 are :

-The use of the function lag

-It doesn't increment the rank, I have this kind of result :

 

Player   Rank

P1           1

P2           1

P3           1

P4           2

 

 

Instead of :

 

 

Player   Rank

P1           1

P2           1

P3           1

P4           4

Super User
Posts: 6,629

Re: Retain increment

OK, one more time ...

 

YOU CANNOT DO THIS:

 

ELSE rank=rank+1 AND i=0;

 

Well, you can do it, but the answer is wrong.  To DO more than one thing, you must use DO:

 

ELSE DO;

   rank = rank + 1;

   i=0;

END;

 

That doesn't mean that all the rest of your code is correct, but this part must be fixed if you are to ever get the result you want.

Trusted Advisor
Posts: 1,309

Re: Retain increment

I do not understand the incentive to use the lag function here, when the use of BY groups is much more straightforward.  It provides an easy way to count the number of consecutive tied results, as well as identifying when a new group has started.  When starting a new group take the last rank, add the number of duplicates +1 to get the new rank.

 

I see by your data image that you want to start rank=1 with each new year.  So here is a modified program that does what you have shown you want.  It starts each year with a rank=1;

 

data want (drop=ndupes);

  set have;

  by year gold silver bronze notsorted;

  retain rank ndupes;

  if first.bronze then do;

    if first.year then rank=1;

    else rank+ndupes+1;

    ndupes=0;

  end;

  else ndupes+1;

run;

 

 

Super User
Posts: 6,629

Re: Retain increment

@mkeintz,

 

I strongly agree, and absolutely would take your approach if programming this myself.  However, I feel that this board should address several purposes.  Some of my thoughts along those lines (not all of which apply to this question) ...

 

The answers should be answers that the poster can understand.  It is a disservice to give a hashing solution when the poster is asking about the proper syntax for IF/THEN statements.  It doesn't matter whether the hashing solution works.  Suggesting code that a poster couldn't possibly understand is wrong (even dangerous).

 

Unless a poster already knows a fair amount of SAS, it is a disservice to provide a macro language solution.  It would be a better use of the poster's time to learn more about SAS and forget about macro language for a year.

 

Sometimes it is a judgment call ... do I correct what the poster started out to do, or do I start from scratch.  Which direction will be a better learning experience for the poster?

 

Given that this is a message board with limited interaction and not a classroom, I emphasize solutions that resemble the poster's code if at all possible.  I expect that to be the best way for the poster to build upon what s/he has already learned.

 

All FWIW, and all JMO.  Clearly, many smart people post here but disagree with these ideas.

 

 

Contributor
Posts: 21

Re: Retain increment

Nobody ?

Super User
Super User
Posts: 9,407

Re: Retain increment

I refer you back to my post above.  Post test data in the form of a datastep, and provide example output.  This guidance will help you post good questions to get good answers.  

Ask a Question
Discussion stats
  • 13 replies
  • 250 views
  • 1 like
  • 5 in conversation