topic Re: Regarding codition in SAS EG in SAS Enterprise Guide

Regarding codition in SAS EG

Sourav_sas — Fri, 08 Jun 2018 09:33:22 GMT

I have one scenario

Here is one small dataset

BUS_ID	UWY	Status
A1008067	2010
A1007937	2015
A1009536	2012
A1005405	2013
A1008311	2018
A1009536	2005
A1005502	2001
A1005524	2000
A1008067	1998

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.

Re: Regarding codition in SAS EG

RW9 — Fri, 08 Jun 2018 10:06:06 GMT

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;

Re: Regarding codition in SAS EG

Sourav_sas — Sat, 09 Jun 2018 07:44:22 GMT

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

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

Re: Regarding codition in SAS EG

Sourav_sas — Sat, 09 Jun 2018 14:29:29 GMT

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

Re: Regarding codition in SAS EG

FredrikE — Mon, 11 Jun 2018 08:52:50 GMT

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