topic Identifying all first occurrences of a value in a variable in New SAS User

Identifying all first occurrences of a value in a variable

Wafflecakes — Thu, 07 Feb 2019 03:03:46 GMT

Hello. I would like to flag all first occurrences of a value in Numberofcars when Numberofcars is >1 by participant id. I have data that looks like this:

ID Numberofcars WantFlag HaveFlag

1 1 . .

1 2 1 1

1 3 1 1

1 3 . 1

2 1 . .

2 2 1 1

2 2 . 1

2 3 1 1

I wrote this code:

proc sort data=have out=want;

by ID Numberofcars;

run;

data want;

set want;

retain test;

if first.ID & first.Numberofcars then test=0;

haveflag=0;

if Numberofcars>1 & test=0 then do;

haveflag=1;

end;

run;

As you can see in haveflag (which is generated from the above code), it equals 1 for ALL occurrences of Numberofcars=3. I only want it to do this for the first one and any other unique values of Numberofcars that does not equal 1.

Re: Identifying all first occurrences of a value in a variable

ScottBass — Thu, 07 Feb 2019 02:48:36 GMT

I'm still unsure what you want? Can you post "have" and "want" data steps, using data lines so we can just c&p and run it?

Re: Identifying all first occurrences of a value in a variable

Wafflecakes — Thu, 07 Feb 2019 03:04:29 GMT

Hi Scott. I edited my original post. Does it make more sense? I don't know what the "want" data steps should look like...

Re: Identifying all first occurrences of a value in a variable

Astounding — Thu, 07 Feb 2019 03:24:23 GMT

Add to your knowledge base: sorting the data is not enough to create first. and last. variables in a DATA step. You also must add a BY statement to the DATA step.

This should work;

Proc sort data=have;
By ID numberofcars;
Run;

Data want;
Set have,
By ID numberofcars;
If first.numberofcars and
Numberofcars > 1 then flag=1;
Run;

Re: Identifying all first occurrences of a value in a variable

Wafflecakes — Thu, 07 Feb 2019 03:31:20 GMT

Ahhh sorry. I forgot to write that in the original post. Still, it works now! Thanks Astounding.

Re: Identifying all first occurrences of a value in a variable

ScottBass — Thu, 07 Feb 2019 03:44:35 GMT

@ScottBass wrote:
I'm still unsure what you want? Can you post "have" and "want" data steps, using data lines so we can just c&p and run it?

You've got your answer, but to explain my post...

If I cut-and-paste this into SAS:

ID Numberofcars WantFlag HaveFlag 

1 1  . . 

1 1 . .

1 2 1 1

1 3 1 1

1 3  . 1

2 1 . .

2 2 1 1

2 2 . 1

2 3 1 1

I get an error.

If I cut-and-paste this into SAS, it works:

data have;
   length ID Numberofcars WantFlag HaveFlag 8;
   input ID Numberofcars WantFlag HaveFlag 8;
   datalines; 
1 1  . . 
1 1 . .
1 2 1 1
1 3 1 1
1 3  . 1
2 1 . .
2 2 1 1
2 2 . 1
2 3 1 1
;
run;

Same for "want" - use datalines to show me what you want as your desired output.

Put your SAS code in a SAS block - click the "running man" icon in the Rich Text editor.

Make it easy for me to help you. It's a slow day otherwise I wouldn't have bothered...

I'm glad Astounding figured out what you wanted. He's smarter than me 🙂

Re: Identifying all first occurrences of a value in a variable

Astounding — Fri, 08 Feb 2019 14:40:05 GMT

@ScottBass ,

You've always provided good and thoughtful answers. It's a pleasure to see you on the board, as time allows.