Solved
Contributor
Posts: 58

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

 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

Accepted Solutions
Solution
‎11-20-2017 07:22 AM
PROC Star
Posts: 1,401

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

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;``````

All Replies
Solution
‎11-20-2017 07:22 AM
PROC Star
Posts: 1,401

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

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;``````
PROC Star
Posts: 1,401

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

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

Super User
Posts: 9,840

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

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.

☑ This topic is solved.