SAS Support Communities

Good day.   I want to create a formula in SAS Visual Analytics, based on the entries as shown below, I want to only sum the 1st entry per Ref number. for example, in the below table we are only looking at Fraud type 4. Suspicious, currently the total rand value is 3 636 910.90 but it should be 1 818 455.45 if I should only calculate based on the 1st line entry. Fraud Type Fraud Documents Suspicious Avg Principle Debt 4. Suspicious 4.1.1 Suspicious 290 727.49 4. Suspicious 4.1.2 False Employer 290 727.49 4. Suspicious 4.1.2 False Employer 541 537.48 4. Suspicious 4.1.1 Suspicious 541 537.48 4. Suspicious 4.1.1 Suspicious 501 207.50 4. Suspicious 4.1.2 False Employer 501 207.50 4. Suspicious 4.1.2 False Employer 193 649.49 4. Suspicious 4.1.1 Suspicious 193 649.49 4. Suspicious 4.1.1 Suspicious 291 333.49 4. Suspicious 4.1.2 False Employer 291 333.49 ... View more

Hi , please will someone be able to assist me - i have the following code (Code will follow) .  i need this code to read 12 months of data the data sets are as follow: how will i create a Macro to read the code with all these data sets.    werkwm.martaccount202210 werkwm.martaccount202211 werkwm.martaccount202212 werkwm.martaccount202301 werkwm.martaccount202302 werkwm.martaccount202303 werkwm.martaccount202304 werkwm.martaccount202305 werkwm.martaccount202306 werkwm.martaccount202307 werkwm.martaccount202308 werkwm.martaccount- these data sets are for the prev months as its not labled- but will update at month end with 202309   Werkwm.martvtraxapplications202210 Werkwm.martvtraxapplications202211 Werkwm.martvtraxapplications202212 Werkwm.martvtraxapplications202301 Werkwm.martvtraxapplications202302 Werkwm.martvtraxapplications202303 Werkwm.martvtraxapplications202304 Werkwm.martvtraxapplications202305 Werkwm.martvtraxapplications202306 Werkwm.martvtraxapplications202307 Werkwm.martvtraxapplications202308 Werkwm.martvtraxapplications-these data sets are for the prev months as its not labled- but will update at month end with 202309   WerkWM.MARTVTRAXDEALERS202210 WerkWM.MARTVTRAXDEALERS202211 WerkWM.MARTVTRAXDEALERS202212 WerkWM.MARTVTRAXDEALERS202301 WerkWM.MARTVTRAXDEALERS202302 WerkWM.MARTVTRAXDEALERS202303 WerkWM.MARTVTRAXDEALERS202304 WerkWM.MARTVTRAXDEALERS202305 WerkWM.MARTVTRAXDEALERS202306 WerkWM.MARTVTRAXDEALERS202307 WerkWM.MARTVTRAXDEALERS202308 WerkWM.MARTVTRAXDEALERS- these data sets are for the prev months as its not labled- but will update at month end with 202309   The code: data FloorplanA (keep = keyloaddate Dealref ArrDays  Prindebt Term RateOffset DealerCode DealerName KeyIBIS ClientName MonthBook MatureDate); set werkwm.martaccount202210; where Accom_FinDescript = 'Floorplan'  and Accom_Repstatus ne 'Closed'  and MonthBook = &effmonth; rename AccoM_ArrDays = ArrDays AccoM_Prindebt=Prindebt AccoM_Term=Term AccoM_RateOffset=RateOffset DealerCode=DealerCode KeyIBIS=KeyIBIS Client_Name=ClientName MonthBook=MonthBook AccoM_MatureDate=MatureDate; LABEL ArrDays='Arrival Days'  Prindebt='Principal Debt'  Term='Term in Months'  RateOffset='Rate Offset'  DealerCode='Dealer Code'  DealerName='Dealer Name'  KeyIBIS='KeyIBIS'  ClientName='Client Name'  MonthBook='Month Booked'  MatureDate='Maturity Date'; run;   Data FloorplanAll; set FloorplanA FloorplanB; run;   /*Daily Tables gevat in die stap- omdat die table tables upate en daar nie veel veranderinge is nie.*/ data FloorplanApps (keep= VTraxM_varCustomerNo VTraxM_decPrice  VTraxM_decDocFee  VTraxd_decPrincipleDebt   VTraxM_decServiceFee  VTraxM_decTotalInterest  VTraxM_varLegalName  VTraxM_varCurrStatus  VTraxM_dtCurrStatus  VTraxM_KeyDateSettle  decDaysOnBook  KeyIBIS   rename =  (VTraxM_varCustomerNo=CustomerNo  VTraxM_decPrice=Price  VTraxM_decDocFee=DocFee  VTraxM_decPrincipleDebt=PrincipleDebt  VTraxM_decServiceFee=ServiceFee  VTraxM_decTotalInterest=TotalInterest  VTraxM_varLegalName=LegalName VTraxM_varCurrStatus=CurrStatus  VTraxM_dtCurrStatus=CurrStatusDate VTraxM_KeyDateSettle=DateSettled  decDaysOnBook=DaysOnBook)); label CustomerNo="Customer Number" Price="Price" DocFee="Document Fee" PrincipleDebt="Principle Debt" ServiceFee="Service Fee" TotalInterest="Total Interest" LegalName="Legal Name" CurrStatus="Current Status" CurrStatusDate="Current Status Date" DateSettled="Date Settled" DaysOnBook="Days On Book"; set Werkwd.martvtraxapplications202210; where KeyIBIS ne (.); run;   Proc sql; create table FloorplanJoin as select a.*,b.* from FloorplanAll a left join FloorplanApps b on a.KeyIBIS=b.KeyIBIS; quit;   proc sort data= FloorplanJoin; by Dealercode DealerName; run;   data Floorplan2 (keep= VTraxM_varCustomerNo VTraxM_bActive VTraxM_varDealerCode  VTraxM_varLegalName VTraxM_decServiceFees VTraxM_decFacilityAmount VTraxM_varFacilityType VTraxM_decFacilityUtilized VTraxM_decFacilityBalance VTraxM_varDealerName VTraxM_varDealerCode rename= (VTraxM_varCustomerNo= CustomerNo VTraxM_bActive= Active VTraxM_varDealerCode= DealerCode VTraxM_varLegalName=LegalName VTraxM_decServiceFees = ServiceFees VTraxM_decFacilityAmount= FacilityAmount VTraxM_varFacilityType =FacilityType VTraxM_decFacilityUtilized = FacilityUtilized VTraxM_decFacilityBalance= FacilityBalance VTraxM_varDealerName = VarDealerName VTraxM_varDealerCode = VarDealerCode)); label CustomerNo = "Customer Number" Active = "Active Status" DealerCode = "Dealer Code" LegalName = "Legal Name" ServiceFees = "Service Fees" FacilityAmount = "Facility Amount" FacilityType = "Facility Type" FacilityUtilized = "Facility Utilized" FacilityBalance = "Facility Balance" VarDealerName = "Variable Dealer Name" VarDealerCode = "Variable Dealer Code"; set  WerkWD.MARTVTRAXDEALERS202210;   if VTraxd_decFacilityAmount eq 0 and VTraxd_decfacilityBalance eq 0 then Delete;   if  VTraxd_bActive ne 1 then delete; run;   proc sort data= Floorplan2; by VarDealerCode VarDealerName; run;   Proc sql; create table Floorplan3 as select a.*,b.*  from FloorplanJoin a left join Floorplan2 b on a.CustomerNo=b.CustomerNo; quit;   proc sort data=Floorplan3; by VarDealerCode FacilityAmount RateOffset DocFee ServiceFees FacilityType FacilityUtilized FacilityBalance; run;   /* Create new variables that keep the first values for each group */ data FloorPlanDaily; set Floorplan3; by VarDealerCode FacilityAmount  RateOffset DocFee  ServiceFees FacilityType  FacilityUtilized FacilityBalance;   /* If this is the first observation for a group, then keep the values */ if first.VarDealerCode  and first.FacilityAmount and first.RateOffset  and first.DocFee  and first.ServiceFees  and first.FacilityType  and first.FacilityUtilized  and first.FacilityBalance then do; new_FacilityAmount = FacilityAmount; new_RateOffset = RateOffset; new_DocFee = DocFee; new_ServiceFees = ServiceFees; new_FacilityType = FacilityType; new_FacilityUtilized = FacilityUtilized; new_FacilityBalance = FacilityBalance; end;   /* Otherwise, assign missing values */ else do; new_FacilityAmount = .; new_RateOffset = .; new_DocFee = .; new_ServiceFees = .; new_FacilityType = .; new_FacilityUtilized = .; new_FacilityBalance = .; end;   /*Dropping the Old fields  */ drop FacilityAmount ArrDays RateOffset DocFee ClientName ServiceFees FacilityType FacilityUtilized FacilityBalance; run;   Thank you for your assistance.  ... View more