- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Though shouldn't your second data set with positives only contain 5 observations?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.