CODEWITHSUNDEEP
arrayssas
Black
Black

Reputation: 4644

SAS: Compare values in a column

I am trying to loop through a column with 50000 rows. I would like to compare the value in say i with (i+1). The only way I know how to do this is by defining an array. However, there is only one variable i.e. The variables column name e.g. Col but 50000 observations within the column. When I use:

  array Transform {50000} Col

where Transform is the name of the array and Col is the column name in my dataset, I receive a subscript error as there are too few variables i.e. Only 1 vs 50000. I have tried replacing {50000} with {50000,1} (and even {*}) so the compiler recognizes that there are 50k observations and only one column. Further I have attempted to transpose the dataset but this seems difficult as I need to add on another variable on to the dataset later which depends on the values of i and (i+1).

Is there a method to loop through the column to compare i and (i+1) using any method (not necessarily an array)? Thanks for the help :)

Upvotes: 1

Views: 4005

Answers (3)

vasja
vasja

Reputation: 4792

Example of using LAG:

data input;
    infile cards;
    input transform;
cards;
3
5
8
12
16
;
run;

data comp;
    set input;
    transform_change = transform - lag1(transform);
run;

For reversed order of rows:

data input_rownum / view=input_rownum;
set input;
rownum = _N_;
run;

proc sort data=input_rownum out=input_reversed;
by descending rownum;
run;

data comp_reverse;
    set input_reversed;
    transform_change = transform - lag1(transform);
run;

LAG1 means previous value of the variable. LAG2 is for pre-previous, and so on. Consult the documentation for more.

Upvotes: 4

Longfish
Longfish

Reputation: 7602

Arrays work across variables, so aren't suitable for your task here. There's a couple of options for you, given the small number of rows the easiest is probably to just join the dataset on itself, with the row number offset by one. You can then do your comparison.

data want;
merge have have (firstobs=2 rename=(col=col_plus1));
run;

Upvotes: 3

Zfunk
Zfunk

Reputation: 1193

If you only want to compare row i with i+1 you could use the lag function. This pulls the value from the previous row read (beware when using this with loops as not all rows will be processed in a loop)

Upvotes: 1

Related Questions