topic Re: Retain increment in SAS Programming

Retain increment

Num19 — Wed, 10 Jan 2018 15:13:16 GMT

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.

Re: Retain increment

RW9 — Wed, 10 Jan 2018 15:21:14 GMT

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-data-AKA-generate/ta-p/258712

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!

Re: Retain increment

ballardw — Wed, 10 Jan 2018 15:24:22 GMT

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;

Re: Retain increment

Astounding — Wed, 10 Jan 2018 15:34:08 GMT

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.

Re: Retain increment

Num19 — Wed, 10 Jan 2018 16:09:12 GMT

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

Re: Retain increment

ballardw — Thu, 11 Jan 2018 15:32:29 GMT

@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.

Re: Retain increment

Num19 — Wed, 10 Jan 2018 18:32:04 GMT

I precise it because I can't export datas.

Here is an example of what I have :

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

Sorry for the pic RW9.

Re: Retain increment

mkeintz — Wed, 10 Jan 2018 18:47:19 GMT

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?

Re: Retain increment

Num19 — Wed, 10 Jan 2018 19:15:08 GMT

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

Re: Retain increment

Num19 — Wed, 10 Jan 2018 20:54:52 GMT

Nobody ?

Re: Retain increment

Astounding — Wed, 10 Jan 2018 21:07:55 GMT

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.

Re: Retain increment

mkeintz — Wed, 10 Jan 2018 22:07:26 GMT

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;

Re: Retain increment

Astounding — Wed, 10 Jan 2018 22:21:57 GMT

@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.

Re: Retain increment

RW9 — Thu, 11 Jan 2018 09:16:16 GMT

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.