topic String comparison problems with lag functions in SAS Programming

String comparison problems with lag functions

CJ_Jackson — Tue, 26 Jan 2016 16:45:06 GMT

I'm having issues with string comparison with when it comes to lag function.

Say I have the following dataset:

DATA test;
	input id code $;
	cards;
	1 abc
	1 abc
	2 xyz
	2 xyy
	2 xyy
	2 zzz
	3 ccc
	3 ccc
	;
PROC SORT;
	by id code;
run;

Then I want to calculate how many distinct code values there are within each ID values, I use this program:

DATA test2;
	SET test;
	by id code;
	RETAIN code_cnt 1;
	ff = first.id;
	lagged = lag1(code);
	if first.id then code_cnt = 1;
	else do;
		if code ~= lag1(code) then code_cnt = code_cnt + 1;
		end;
PROC PRINT;
run;

The resulting dataset looks like this:

Obs id code code_cnt ff lagged

1	abc	1	1
1	abc	2	0	abc
2	xyy	1	1	abc
2	xyy	2	0	xyy
2	xyz	3	0	xyy
2	zzz	4	0	xyz
3	ccc	1	1	zzz
3	ccc	2	0	ccc

What is wrong?

Re: String comparison problems with lag functions

LinusH — Tue, 26 Jan 2016 16:52:10 GMT

The count (distinct code) construct in SQL seems better suited for this kind of calculation.

Re: String comparison problems with lag functions

DanZ — Tue, 26 Jan 2016 17:01:19 GMT

I agree that proc sql is generally a better solution for most types of aggregation. But, if it must be in a data step for some reason, then it would look something like this:

DATA test;
	input id code $;
	cards;
	1 abc
	1 abc
	2 xyz
	2 xyy
	2 xyy
	2 zzz
	3 ccc
	3 ccc
	;
PROC SORT;
	by id code;
run;

DATA test2;
	SET test;
	by id code;
	retain code_cnt;
	if first.id then code_cnt = 0;
	code_cnt = code_cnt + 1;
	if last.id then output;
run;	

PROC PRINT;
run;

This is probably what the proc sql will be doing in the background as well.

Re: String comparison problems with lag functions

Reeza — Tue, 26 Jan 2016 17:05:33 GMT

You can't use lag in IF/THEN conditions, the behaviour isn't what you expect. It's a common issue when first starting out with the lag function. If you google the terms you'll find many paper/posts on the issue.

DATA test2;
	SET test;
	by id code;
	RETAIN code_cnt 1;
	ff = first.id;
	lagged = lag1(code);
	if first.id then code_cnt = 1;
	else do;
		if code ne lagged then code_cnt = code_cnt + 1;
		end;
PROC PRINT;
run;

As mentioned PROC SQL is a better method here:

proc sql;
create table want as
select id, count(distinct code) as code_cnt
from test;
quit;

Or a SAS proc method:

proc sort data=test out=test2 nodupkey;
by id code;
run;

proc freq data=test2;
table ID/out=want_freq;
run;

proc print data=want_freq;
run;

EDIT: One more option - based on using FIRST since you've sorted and are using BY variables:

PROC SORT;
	by id code;
run;

data want;
set test;
by id code;
if first.id then code_cnt=0;
if first.code then code_cnt+1;
*if last.id then output;
run;

Re: String comparison problems with lag functions

Kurt_Bremser — Tue, 26 Jan 2016 17:56:18 GMT

The lag() function builds a FIFO chain that receives a value everytime the function is called. So calling the function in a conditional branch or a do loop will lead to unexpected behaviour.

Re: String comparison problems with lag functions

CJ_Jackson — Tue, 26 Jan 2016 18:13:23 GMT

thanks. This is a weird behavior. So if I first saved the lag1(code) as a new variable then I would be able to use that value in a conditional then, correct?

Re: String comparison problems with lag functions

CJ_Jackson — Tue, 26 Jan 2016 18:35:08 GMT

Thanks, your solutions (mostly) worked. In the PROC SQL you wanna do GROUP BY id as well.

Re: String comparison problems with lag functions

ballardw — Wed, 27 Jan 2016 00:24:28 GMT

@CJ_Jackson wrote:
thanks. This is a weird behavior. So if I first saved the lag1(code) as a new variable then I would be able to use that value in a conditional then, correct?

Yes, exactly as @Reeza did with the variable LAGGED in the first data step of the solution.

1	abc	1	1
1	abc	2	0	abc
2	xyy	1	1	abc
2	xyy	2	0	xyy
2	xyz	3	0	xyy
2	zzz	4	0	xyz
3	ccc	1	1	zzz
3	ccc	2	0	ccc

1	abc	1	1
1	abc	2	0	abc
2	xyy	1	1	abc
2	xyy	2	0	xyy
2	xyz	3	0	xyy
2	zzz	4	0	xyz
3	ccc	1	1	zzz
3	ccc	2	0	ccc

1	abc	1	1
1	abc	2	0	abc
2	xyy	1	1	abc
2	xyy	2	0	xyy
2	xyz	3	0	xyy
2	zzz	4	0	xyz
3	ccc	1	1	zzz
3	ccc	2	0	ccc