topic Re: create a separate subset based on positive and negative value in a group in SAS Programming

create a separate subset based on positive and negative value in a group

subrat1 — Mon, 20 Nov 2017 07:47:40 GMT

Policy No	Gross Written Amt
9019374	1,260.00
9019374	0
9019374	1,260.00
9019374	-1,260.00
9019468	0
9019468	948
9019557	0
9019557	-1,040.00
9013670	0
9013670	1,540.00
9013670	1,540.00

If any policy having Gross written amount negative then create a separate dataset.

So my output should be two dataset:

Policy No	Gross Written Amt
9019374	1,260.00
9019374	0
9019374	1,260.00
9019374	-1,260.00
9019557	0
9019557	-1,040.00

If any policy having Gross written amount positive then create a separate dataset

another dataset should contains:

Policy No	Gross Written Amt
9019468	0
9019468	948
9013670	1,540.00
9013670	1,540.00

Re: create a separate subset based on positive and negative value in a group

PeterClemmensen — Mon, 20 Nov 2017 08:02:33 GMT

Something like this?

data have;
input Policy_No$ Gross_Written_Amt;
datalines;
9019374 1260
9019374 0
9019374 1260
9019374 -1260
9019468 0 
9019468 948
9019557 0 
9019557 -1040
9013670 0
9013670 1540
9013670 1540
;

proc sql;
	create table neg_pol as
	select * from have 
	where Policy_No in
	(select Policy_No from have where Gross_Written_Amt<0);
	create table pos_pol as 
	select * from have 
	where Policy_No not in
	(select Policy_No from neg_pol);
quit;

Re: create a separate subset based on positive and negative value in a group

PeterClemmensen — Mon, 20 Nov 2017 08:03:51 GMT

Though shouldn't your second data set with positives only contain 5 observations?

Re: create a separate subset based on positive and negative value in a group

RW9 — Mon, 20 Nov 2017 09:15:59 GMT

Out of interest, why would you want to create two datasets? Its far simpler for programming to just assign a group value in the current data (also smaller storage) and then use that as a by group in further processing. For instance if you wanted to get means of the data, by pos/neg, in your example you would then need to write two proc means, in mine only one, with a by group. Its rarely a good idea to split data out.