topic Re: Sas Macro Help. in SAS Programming

Sas Macro Help.

MJM11111 — Thu, 26 Oct 2023 08:45:21 GMT

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.

Re: Sas Macro Help.

yabwon — Thu, 26 Oct 2023 09:03:27 GMT

Do you really need a macro to read in that datasets? Why not to use "data sets list", see example:

libname werkwm (work);
data
  werkwm.martaccount202210
  werkwm.martaccount202211
  werkwm.martaccount202212
  werkwm.martaccount202301
  werkwm.martaccount202302
  werkwm.martaccount202303
  werkwm.martaccount202304
  werkwm.martaccount202305
  werkwm.martaccount202306
  werkwm.martaccount202307
  werkwm.martaccount202308
  werkwm.martaccount
  ;
  set  sashelp.class;
run;

options noDSNFERR; /* to use data sets list (e.g., SET A1-A111;)
                      and don't get error if any from the list is missing */
data ALL;
  set 
    werkwm.martaccount202210-werkwm.martaccount202308 werkwm.martaccount
  ;
run;
options DSNFERR;

[EDIT:]

The SET statement with a "data sets list" of the form A1-A5 usually expects that all datasets A1, A2, A3, A4, and A5 exist. In you case the range

werkwm.martaccount202210-werkwm.martaccount202308

for sure will have some "gaps" (we don expect to have 202214,202215,etc.) that's why the noDSNFERR option solves the problem, basicall it says to SAS: "if a data set from the range does not exist ignore that and go to the next one"

Bart

Re: Sas Macro Help.

MJM11111 — Thu, 26 Oct 2023 09:21:00 GMT

I can do that, but the problem is , i want to automate this piece of work, so wont it be better to just have the macro running to read the data whole time ?

Re: Sas Macro Help.

yabwon — Thu, 26 Oct 2023 09:34:43 GMT

Then how about something like this:

libname werkwm (work);
data
  werkwm.martaccount202210
  werkwm.martaccount202211
  werkwm.martaccount202212
  werkwm.martaccount202301
  werkwm.martaccount202302
  werkwm.martaccount202303
  werkwm.martaccount202304
  werkwm.martaccount202305
  werkwm.martaccount202306
  werkwm.martaccount202307
  werkwm.martaccount202308
  werkwm.martaccount
  ;
  set  sashelp.class;
run;

%macro ListFiles(lib,ds,date);
%local start end;
%let end  = %sysfunc(intnx(Month,"&date."d,-1 ,S),yymmn6.);
%let start= %sysfunc(intnx(Month,"&date."d,-12,S),yymmn6.);
&lib..&ds.&start. - &lib..&ds.&end. &lib..&ds.
%mend;

options noDSNFERR; /* to use data sets list (e.g., SET A1-A111;)
                      and don't get error if any from the list is missing */
data ALL;
  set 
    %ListFiles(werkwm,martaccount,1sep2023);
  ;
run;
options DSNFERR;

The code still uses "noDSNFERR" option.

The "date" parameter should be set for "month with no date in dataset name", in this case September 2023.

Add "options Mprint;" to see how it is resolved under the hood.

Bart

Re: Sas Macro Help.

LinusH — Thu, 26 Oct 2023 10:11:03 GMT

It's a bit hard to know your exact requirement, but if you need a list of datasets for a 12 month period, here's some (untested) code you can start with. If you want to fully automate you can use &sysdate in the beginning to specify your start period. Since I didn't know how you'll select the 12 month period I didn't include that in my logic.

%let start=%sysfunc(intnx(month,%sysfunc(inputn(&end.01,yymmdd8.)),-12), yymmddn8.);
%put &start;

proc sql;
	select catx('.',libname,memname) into: martaccounts separated by ' '
		from dictionary.tables
		where memname between "MARTACCOUNT&start" and "MARTACCOUNT&end"
			and libname = 'WERKWM';
	select catx('.',libname,memname) into: martvtraxapplications separated by ' '
		from dictionary.tables
		where memname between "MARTVTRAXAPPLICATIONS&start" and "MARTVTRAXAPPLICATIONS&end"
			and libname = 'WERKWM';
	select catx('.',libname,memname) into: martvtraxdealers separated by ' '
		from dictionary.tables
		where memname between "MARTVTRAXDEALERS&start" and "MARTVTRAXDEALERS&end"
			and libname = 'WERKWM';
quit;