BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
subrat1
Fluorite | Level 6
Policy NoGross Written Amt
90193741,260.00
90193740
90193741,260.00
9019374-1,260.00
90194680
9019468948
90195570
9019557-1,040.00
90136700
90136701,540.00
90136701,540.00

 

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

So my output should be two dataset:

Policy NoGross Written Amt
90193741,260.00
90193740
90193741,260.00
9019374-1,260.00
90195570
9019557-1,040.00

 

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

another dataset should contains:

Policy NoGross Written Amt
90194680
9019468948
90136701,540.00
90136701,540.00

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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;
PeterClemmensen
Tourmaline | Level 20

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1169 views
  • 0 likes
  • 3 in conversation