Desktop productivity for business analysts and programmers

Regarding codition in SAS EG

Reply
Contributor
Posts: 74

Regarding codition in SAS EG

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.

 

Super User
Super User
Posts: 9,416

Re: Regarding codition in SAS EG

Posted in reply to Sourav_sas

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;
Contributor
Posts: 74

Re: Regarding codition in SAS EG

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

Contributor
Posts: 74

Re: Regarding codition in SAS EG

Posted in reply to Sourav_sas

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

Super Contributor
Posts: 368

Re: Regarding codition in SAS EG

Posted in reply to Sourav_sas

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

 

Ask a Question
Discussion stats
  • 4 replies
  • 153 views
  • 0 likes
  • 3 in conversation