BookmarkSubscribeRSS Feed
Sourav_sas
Quartz | Level 8

Hi

 

I have one scenario

 

Here is one small dataset

BUS_IDUWYStatus
A10080672010 
A10079372015 
A10095362012 
A10054052013 
A10083112018 
A10095362005 
A10055022001 
A10055242000 
A10080671998 

 

We have three columns, Business_ID(BUS_ID), Under_Writting_Year(UWY) and last column as Status.

Suppose I am using one parameter to run the report containing this dataset. That parameter is UWY. That parameter has two value &max_uwy and &min_uwy.

Suppose one BUS_ID is in the &max_uwy  available but not available in the &min_uwy, it means it is new, not repeating, then the status column will be "New" or else it will be "Renew"

For example:

if the  report was run of UWY 2017, if this (Business ID),  has no records in UW 2016 , then the Status is “New”, else its “Renew

 

I hope I am able to make you understand.

 

Please help me to solve the issue.

 

Let me know any doubt there.

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

At a guess (post test data in the form of a datastep, and show your code and required output):

data want;
  set have;
  if input("&max_uwy.",best.)=uwy and lag(uwy ne input("&max_uwy.",best.)-1) then status="New";
  else if input("&max_uwy.",best.)=uwy then status="Renew";
run;
Sourav_sas
Quartz | Level 8

Hi

 

Thanks for your reply. I have attached one screenshot of sample dataset. with that can you try this scenario. and explained one prompt also. That is uwy. This prompt type should be like

Capture.JPG

For that I have given the example.

 if the  report was run of UW  year 2017, if this contract (Business ID),  has no records in UW 2016 , then the Contract is "New" else this is "Renew".

I have one sample code that is I am taking from my production code....

proc sql;
	create table work.out as
		select 
			t1.BUS_ID,
			t1.UWYEAR ,
			if input("&uwy_max.",best.)=uwy and lag(uwy ne input("&max_uwy.",best.)-1) 
			then status="New";
  			else if input("&max_uwy.",best.)=uwy then status="Renew";

		from work.dummydata t1
		where 
		%STP_WHERE_PARAM(t1.BUS_ID,bus_id,IN, TYPE=S)
				and &UWY_MN. <= t1.UWYEAR <= &UWY_MX.
			order by BASE_COMPANY_TITLE,
				BUS_ID,
				UWYEAR
	;
quit;

Here I have used only two columns (BUS_ID and UWY) these both are available in that sample dataset which I have shared on my first post. Here I have used two prompts, BUS_ID and UWY. For STP prompt creation we use DI Studio and we have some defined macro for prompt calling, like %STP_WHERE_PARAM.

 

&UWY_MIN and &UWY_MX these part of prompt. bus_id also one prompt. bus_id is dynamic selection prompt, and uwy is range type prompt.

 

Here I want add one column that is Status. For status condition as discussed earlier like,

If I ma selecting 2017 as UWY and before that in 2016 no BUS_ID is available, it means that BUS_ID is new, for that we will have status as "NEW", and if we have the same BUS_ID in 2016, it means it is not new, it is repeating, then the status would be "Renew".

 

I hope I am able to make you understand.

Can any one help me to create this condition. Its bit urgent.

 

Regards

Sourav

Sourav_sas
Quartz | Level 8

Hi ca anyone give a suitable solution for this.  I really need for one project. 

FredrikE
Rhodochrosite | Level 12

Why not do it in several steps?

 

First create a table with min/max of UWY for each busid

 

Then lef join that table on your data and check if min/max to see if its new or not.

 

Something like this: If (missing(UWY) or max(min,max) = UWY) then new else renew.

 

//Fredrik

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 825 views
  • 0 likes
  • 3 in conversation