BookmarkSubscribeRSS Feed
Num19
Calcite | Level 5

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.

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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!

ballardw
Super User

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;

Astounding
PROC Star

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.

Num19
Calcite | Level 5

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

ballardw
Super User

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

Num19
Calcite | Level 5

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.

mkeintz
PROC Star

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?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Num19
Calcite | Level 5

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

Astounding
PROC Star

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.

mkeintz
PROC Star

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;

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Astounding
PROC Star

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

 

 

Num19
Calcite | Level 5

Nobody ?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1974 views
  • 1 like
  • 5 in conversation