topic Re: Adding ID field based on another varible that includes missing values in SAS Procedures

Adding ID field based on another varible that includes missing values

jerry898969 — Fri, 28 Feb 2014 15:25:30 GMT

Hello,

I have a data table that has rows that I want to give a number too based on a variable. For example

I have the following data:

col1

------------

I want to create another variable called contentid. The data should be like this

col1 contentid

------------

. 1

20 1

. 1

6 2

. 2

20 3

. 3

20 3

I've used a data step with a retain statement but i'm not sure at what point to increment the value of contentid. The fact there is missing rows is causing me the hardest time.

This is what I have so far

data test ;

length contentid $2 ;

set x ;

retain contentid ;

if _n_ = 1 then contentid = 1;

run ;

Thank you for any help.

Re: Adding ID field based on another varible that includes missing values

Reeza — Fri, 28 Feb 2014 15:48:40 GMT

Actually its the fact that the the first one is a value that makes my logic unclear. How do you know the first one and 20 are the same? Where does the extra missing come from the last series of 20's?

This seems a bit contrived, and not sure it will actually handle your data.

data want;

set have;

retain contentid 1 last;

if col1 ne . then do;

if col1 ne last and last ne . then do;

contentid+1;

last=col1;

end;

else if col1 ne last then do;

last=col1;

end;

run;

Re: Adding ID field based on another varible that includes missing values

jerry898969 — Fri, 28 Feb 2014 15:55:01 GMT

Hi Reeza,

Thank you for the reply.

This is data coming from a data set someone else sends me. It's the back end data file for a front end that shows data based on the content id. The missing will generate a space when the user views the application. That data was just an example of how the data could come to me and I have to tag the rows with the correct content id.

That code worked for the example data I'm using. I will run it against the full data set when it is sent to me.

Thank you so much.