SAS Programming

DATA Step, Macro, Functions and more
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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