DATA Step, Macro, Functions and more

delete all the Geography from rows having zero sum Value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

delete all the Geography from rows having zero sum Value

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.


Accepted Solutions
Solution
‎11-22-2017 06:49 AM
Frequent Contributor
Posts: 80

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

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;

View solution in original post


All Replies
PROC Star
Posts: 1,405

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

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;
Solution
‎11-22-2017 06:49 AM
Frequent Contributor
Posts: 80

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

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;
Super User
Super User
Posts: 9,862

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 155 views
  • 1 like
  • 4 in conversation