topic Re: first./last./retain in SAS Programming

first./last./retain

webart999ARM — Fri, 04 Jun 2021 11:44:22 GMT

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

Re: first./last./retain

Aku — Fri, 04 Jun 2021 11:49:17 GMT

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;

Re: first./last./retain

webart999ARM — Fri, 04 Jun 2021 11:52:06 GMT

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

Re: first./last./retain

PaigeMiller — Fri, 04 Jun 2021 12:00:36 GMT

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.

Re: first./last./retain

webart999ARM — Fri, 04 Jun 2021 12:02:59 GMT

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

02JAN1996

03JAN1996

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

Kind Regards

Artur

Re: first./last./retain

Tom — Fri, 04 Jun 2021 12:05:39 GMT

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.

Re: first./last./retain

PaigeMiller — Fri, 04 Jun 2021 12:06:23 GMT

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

Re: first./last./retain

webart999ARM — Fri, 04 Jun 2021 12:06:12 GMT

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.

Re: first./last./retain

Kurt_Bremser — Fri, 04 Jun 2021 12:06:25 GMT

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.

Re: first./last./retain

PaigeMiller — Fri, 04 Jun 2021 12:08:03 GMT

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

Re: first./last./retain

Kurt_Bremser — Fri, 04 Jun 2021 12:10:53 GMT

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;

Re: first./last./retain

webart999ARM — Fri, 04 Jun 2021 12:16:28 GMT

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

Re: first./last./retain

webart999ARM — Fri, 04 Jun 2021 12:26:18 GMT

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

Re: first./last./retain

Tom — Fri, 04 Jun 2021 12:39:05 GMT

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.

Re: first./last./retain

webart999ARM — Fri, 04 Jun 2021 12:44:43 GMT

Thank you @Tom,

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

Artur