topic Re: delete all the Geography from rows having zero sum Value in SAS Programming

delete all the Geography from rows having zero sum Value

Sfzz — Wed, 22 Nov 2017 10:09:06 GMT

Hi,

Can anyone tell me how can i delete all the Geography from rows which are having zero value while summing for that geography. There are around 10,000 different Geographies in my dataset.

I have attached the excel file for reference.

Re: delete all the Geography from rows having zero sum Value

PeterClemmensen — Wed, 22 Nov 2017 10:16:00 GMT

Something like this?

data have;
input Geography$ Variable_Value;
datalines;
Geo00001 1
Geo00001 3
Geo00001 4
Geo00001 5
Geo00002 0
Geo00002 0
Geo00002 0
Geo00002 0
Geo00003 5
Geo00003 0
Geo00003 0
Geo00003 7
Geo00003 0
Geo00003 2
Geo00004 0
Geo00004 0
Geo00004 0
Geo00004 0
Geo00004 0
;

proc sort data=have;
	by Geography;
run;

/* Data set approach */
data want;
	set have(where=(Variable_Value ne 0));
	by Geography;
	if first.Geography then sum=0;
	else sum+Variable_Value;
	retain sum;
	if last.Geography;
run;

Re: delete all the Geography from rows having zero sum Value

RW9 — Wed, 22 Nov 2017 10:25:44 GMT

Post test data in the form of a datastep, within the body of the post, using the {i} code window.

Re: delete all the Geography from rows having zero sum Value

TarunKumar — Wed, 22 Nov 2017 10:37:54 GMT

data have; input Geography$8. Variable_Value; datalines; Geo00001 1 Geo00001 3 Geo00001 4 Geo00001 5 Geo00002 0 Geo00002 0 Geo00002 0 Geo00002 0 Geo00003 5 Geo00003 0 Geo00003 0 Geo00003 7 Geo00003 0 Geo00003 2 Geo00004 0 Geo00004 0 Geo00004 0 Geo00004 0 Geo00004 0 run; proc sort data=have;by Geography descending Variable_Value;run; data want ; set have; by Geography ; if first.Geography then t = 0; t+Variable_Value; if t ne 0; drop t; run;