topic Re: Create a new variable in New SAS User

Create a new variable

Valor123 — Mon, 24 Aug 2020 11:32:53 GMT

I have a table of data that I want to add an extra line to, indicating if two variables stay the same or if one of them changes.

For example if I had a table like this:

A	1
A	1
A	2
A	3
B	1
B	1
B	4
C	1
C	2
C	2

I would want to add a new column starting at 1 and going up by 1 every time either column 1 or column 2 changes. In the example this would create the following result:

A	1	1
A	1	1
A	2	2
A	3	3
B	1	4
B	1	4
B	4	5
C	1	6
C	2	7
C	2	7

The real dataset is much larger than this, about 400.000 entries, but the principle remains the same. I need an automated and scalable solution. I figured I could turn the columns into arrays, use a for loop to create column 3 and add it to the dataset, but was not able to figure this out.

Any help is appreciated, cheers!

Re: Create a new variable

PeterClemmensen — Mon, 24 Aug 2020 11:39:41 GMT

Try this

data have;
input a $ b;
datalines;
A 1
A 1
A 2
A 3
B 1
B 1
B 4
C 1
C 2
C 2
;

data want;
   set have;
   if lag(a) ne a | lag(b) ne b then c+1;
run;

Re: Create a new variable

novinosrin — Mon, 24 Aug 2020 11:40:16 GMT

Hi @Valor123 Good morning

data have;
input id $ var1;
cards;
A	1
A	1
A	2
A	3
B	1
B	1
B	4
C	1
C	2
C	2
;

data want;
 set have;
 by id var1 ;
 if first.var1 then want+1;
run;

Re: Create a new variable

Valor123 — Mon, 24 Aug 2020 12:07:14 GMT

worked in one go, thank you!