first./last./retain

[webart999ARM]

Good afternoon community.

I have one small simple question for which I would like to ask you for some efficient solutions.

I have the below dataset.

I need to derive new variable Z, which will have unique values for each unique Y variable value(marked with red).

It seems pretty simple but I am struggling to find any effective solution.
I just don't want to use nodupkey/merge back solution which is pretty straightforward.

Thank you in advance for your suggestions.

Kind Regards
Artur

[Aku]

I made z based on y but you can define something other if needed.

Data want;
set have;
by x y notsorted;
Retain z 0;
if First.y then z = y/100;
run;

[webart999ARM]

Dear @Aku 

Thank you for your answer.

Please consider that Y is a test variable, instead of Y in my real dataset I have dates.
I can't apply your solution.

I need data driven solution here.

Thank you 

Kind Regards

Artur

[PaigeMiller]

Hello @webart999ARM 

This example can be interpreted in a number of ways, and I can think of several interpretations of your example, that each would require different programming.

So, please clarify: do you obtain the new column as:

• y/100
• group sequence, each time Y changes the new column is incremented by 1 (even if Y changes by something other than the 100 shown)
• something else

Then you say:

Please consider that Y is a test variable, instead of Y in my real dataset I have dates.
I can't apply your solution.

I need data driven solution here.

Please provide a more meaningful set of data that is representative of your actual problem. And also please explain how the new variable is computed. Please don't make us guess or try to figure this out, by ourselves, how the new variable is created. Explain it clearly. As you can see, we can't figure it out from what you have presented.

[webart999ARM]

Dear, @PaigeMiller 

As you can see from my reply above.
Y is a fake variable here, instead of it I have dates in numerical representation.
E.g.

01JAN1996

01JAN1996

01JAN1996

02JAN1996

02JAN1996

02JAN1996

03JAN1996

03JAN1996

03JAN1996

I need to derive Z variable, which will have(in this example) 3 different values, 1, 2 and 3 accordingly.

Kind Regards

Artur

[PaigeMiller]

@webart999ARM 

Please provide a complete example, showing the inputs and the desired outputs. As I said, don't make us guess.

Please explain clearly how the outputs are derived from the inputs. As I said, don't make us guess.

[Kurt_Bremser]

Looks like you need this (using variable names from your initial post):

data want;
set have;
by x y;
if first.x
then z = 1;
else if first.y then z + 1;
run;

[webart999ARM]

Sorry but I can't explain it in more details.

New variable is NOT computed yet.
If I knew how it should be computed then I would not post this question here.

[PaigeMiller]

---

@webart999ARM wrote:
Sorry but I can't explain it in more details.

New variable is NOT computed yet.
If I knew how it should be computed then I would not post this question here.

---

I'm not asking you for SAS code, I am asking for an explanation, I want you to explain what you want and how you do it in words, as if you were talking to your professor or to your boss.

[Tom · Accepted solution]

Sounds like you want simple counter.

data want;
  set have ;
  by x y ;
  z + first.y;
run;

Note: The new variable Z cannot already exist in the input dataset HAVE. 

[webart999ARM]

Dear @Tom ,

Thank you for your suggested method.

That's what I want to get.

Solution is not quite clear for me, because I used to methods like first., last., retain, for this kind of tasks.

Could you please explain in two wards how this works, I mean the part z+first.y ?

Thank you in advance.

Kind Regards

Artur

[Tom]

This is a SUM statement. 

SAS evaluates boolean expressions to 1 (TRUE) or 0 (FALSE).  So when FIRST.Y is TRUE it has a value of 1.

So when this observation is the first one with this value of Y (within the current value of X) the counter is incremented by 1.

[webart999ARM]

Thank you @Tom,

I will definitely include this kind of solutions in my daily routine work.
Kind Regards

Artur

[Kurt_Bremser]

In addition to what @PaigeMiller said, post your example data in usable form (as code, in a DATA step with DATALINES), so we instantly have something to test code against. We cannot test code with pictures.

[webart999ARM]

Thank you @Kurt_Bremser and @Tom for your suggested methods, that's exactly what I want.

To be honest I don't understand actually how it works without retain statement. If you could explain that would be great, if no then I will try to investigate myself.

My question is pretty simple and I thought it doesn't necessary to provide details like you asked me.
Sorry for not being too clear.

Thank you again for your prompt answers and efficient solutions.

Kind Regards
Artur