topic Re: COmparison between rows of a dataset in SAS Programming

COmparison between rows of a dataset

ArchanaSudhir — Fri, 15 Apr 2016 14:15:23 GMT

HI I have a dataset like this

sort1	sort2	var1	var2	var3	var4	var5	var6
1	1	5	0
2	2	1 2 3 4	7 8 9 5	0.1	0.1	0.1	2
3	2	1 2 3 5	3 4 5 6	0.1	0.1	0.1

If var2 is 0 in the first line ,

I need to set var3,var4,var5,var6 as zero in teh next 2 lines.

How do I do this?

Thanks,

Archana

Re: COmparison between rows of a dataset

andy_smith_amadeus_co_uk — Fri, 15 Apr 2016 14:47:22 GMT

Hi Archana,

This looks like a job for the retain statement to set up a flag.

How about something like:

data new;

retain delflag; * don't reset this value to missing for every iteration;

set old;

if _n_ eq 1 and var2=0 then delflag="Y"; * check for var2=0 only on the first iteration;

if _n_ gt 1 and delflag="Y" then do;

var3=0;

var4=0;

var5=0;

var6=0; * Set to zeros only if correct condition met;

end;

run;

Re: COmparison between rows of a dataset

PGStats — Fri, 15 Apr 2016 15:59:12 GMT

A sum statement creates a retained variable which is useful here

data a;
infile datalines truncover;
input sort1	sort2	var1	var2	var3	var4	var5	var6;
datalines;
1	1	5	0	 	 	 	 
2	2	1 2 3 4	7 8 9 5	0.1	0.1	0.1	2
3	2	1 2 3 5	3 4 5 6	0.1	0.1	0.1	
;

data want;
set a;
if var2 = 0 then doit = 2;
else if doit then do;
    var3=0; var4=0; var5=0; var6=0;
    doit + (-1);
    end;
drop doit;
run;

Re: COmparison between rows of a dataset

Astounding — Fri, 15 Apr 2016 16:21:23 GMT

It gets a little more complex if VAR2 could be 0 on consecutive observations.

Re: COmparison between rows of a dataset

LinusH — Sat, 16 Apr 2016 08:58:47 GMT

Your requirement together with the record layout gives me the feeling that you don't store the data in an optimal way.
Can you tell us the reason for this logic, and what var2 and var3-var6 represents in the real world?