topic Re: Repetitively executing a Maco in SAS Programming

Repetitively executing a Maco

89974114 — Fri, 02 Mar 2018 09:18:47 GMT

I am working with a dataset that in reality will have thousands of segments. For illustration I have enclosed 8.

What I am looking to do is to create a dataset for each segment so I can calculate the contribution of each dataset. I have done the majority of the work but what I am asking is to take every row of this output and turn it into its own dataset.

The data set looks like this

ID    Segment    Asset    Mapping    Performing    Fixed 

1     Loan       Asset    Loan1      Performing    Fixed
2     Loan       Asset    Loan1      No            Fixed
3     Loan       Asset    Loan1      P             Floating
4     Loan       Asset    Loan1      N             floating
5     Loan       Asset    Loan2      P             Fixed
...
8     Loan       Asset    Loan2      N             Floating

The Data is already nicely sorted as above.

The Macro I have written is as follows:

%macro BinData(i);

Data Bin&i;
set Import;
If _N_ = &i ;
run;

%mend;

Is there such a way to repetitively loop this macro for the (in the larger picture) all 8 (1000) segments, something like:

proc
do x=1 to 8;
%bindata(x);
run;

The importance is focused on creating a separate dataset for every segment. TIA.

Re: Repetitively executing a Maco

Kurt_Bremser — Fri, 02 Mar 2018 09:38:29 GMT

Don't split datasets, use by-group processing instead. I guess you wanted to use each single segment to extract data from another dataset?

Re: Repetitively executing a Maco

Satish_Parida — Fri, 02 Mar 2018 09:45:55 GMT

This will server the Purpose.

data have;
input ID:best12.    Segment:$10.    Asset:$10.    Mapping:$10.    Performing:$10.    Fixed:$10.;
datalines;
1     Loan       Asset    Loan1      Performing    Fixed
2     Loan       Asset    Loan1      No            Fixed
3     Loan       Asset    Loan1      P             Floating
4     Loan       Asset    Loan2      N             floating
5     Card       Asset    Card1      P             Fixed
8     Card       Asset    Card2      N             Floating
;
run;

proc sql;
create table list as
select distinct Segment as Segment from have;
quit;
%let cnt=&sqlobs;
%put &cnt;

%macro do_it;

%do i=1 %to &cnt;
	proc sql;
	select Segment into :Segment from list(firstobs = &i obs = &i);
	quit;
	
	data &Segment.;
	set have;
	where Segment="&Segment.";
	run;	
%end;

%mend;

%do_it;

Re: Repetitively executing a Maco

89974114 — Fri, 02 Mar 2018 09:49:08 GMT

I will be performing a very large macro on each segment to calculate a variety of outputs for every segment, then at the end I'll be creating a cumulative sum of all the segment outputs for each Asset and Liability then subtract the two from each other to get a final projection.

So really I need every individual segment to be saved as a datafile to then perform a bunch of operations on each one.

Re: Repetitively executing a Maco

Kurt_Bremser — Fri, 02 Mar 2018 09:56:26 GMT

Still no need to split the dataset. See a simple example:

%macro show_a_person(dataset,index);
proc print data=&dataset (firstobs=&index obs=&index) noobs;
run;
%mend;

data _null_;
set sashelp.class;
call execute('%show_a_person(sashelp.class,'!!put(_n_,best.)!!');');
run;

Re: Repetitively executing a Maco

89974114 — Fri, 02 Mar 2018 10:02:17 GMT

I'm getting some errors with this code. It just returns the Segments and not the resulting columns.

In an ideal world I'd like to just save every row in the order it have been sorted as individual datasets in the work lib.

Re: Repetitively executing a Maco

89974114 — Fri, 02 Mar 2018 10:07:30 GMT

This looks lovely on the Proc Print, cheers.

Although, If I was to continue to take each segment and then put that segment through a vigorous process of extracting the payment schedule, turning it into a maturity profile and so on, Would it be possible to call on each segment to put into my next macro iteratively?

So taking each segment, I'd like to then again loop each segment into another macro which encloses all the procedures I need to apply.

1. Sort the datawarehouse.

2. separate the segments into individual observations / rows / subsets.

3. apply each segment to the macro

4. sum the results of each segment.

Re: Repetitively executing a Maco

Satish_Parida — Fri, 02 Mar 2018 10:08:33 GMT

That is possibly the issue with Variable length and space. I had used strip function. Please find the modified code

And the order wont change in the resulting datasets.

proc sql;
create table list as
select distinct Segment as Segment from have;
quit;
%let cnt=&sqlobs;
%put &cnt;

%macro do_it;

%do i=1 %to &cnt;
	proc sql;
	select Segment into :Segment from list(firstobs = &i obs = &i);
	quit;
	
	data &Segment.;
	set have;
	where strip(Segment)=strip("&Segment."); /*Using Strip*/
	run;	
%end;

%mend;

%do_it;

Re: Repetitively executing a Maco

89974114 — Fri, 02 Mar 2018 10:16:10 GMT

I think the issue that keeps popping up is that this dataset is sorted in the following way

Asset > Mapping > Performing > Fixed

So your code is just returning one column and just the word segment, which is strange but there are more constraints in play than just the segment part

Re: Repetitively executing a Maco

Satish_Parida — Fri, 02 Mar 2018 10:31:57 GMT

@89974114 This is a very strange requirement. I understood you need data per variable segment.

A little modification to @Kurt_Bremser code.

%macro show_a_person(dataset,index);
data &dataset._&index.;
set &dataset.(firstobs=&index obs=&index);
run;
%mend;

data _null_;
set sashelp.class;
call execute('%show_a_person(sashelp.class,'!!put(_n_,best.)!!');');
run;

There you go. Just replace sashelp.class with your work dataset.

Re: Repetitively executing a Maco

89974114 — Fri, 02 Mar 2018 10:47:50 GMT

This code is a lot better looking than the solution I managed to come out with:

%macro BinData;

%do i = 1 %to 8;

Data Bin&i;
set Import;
If _N_ = &i;
run;

%end;

%mend;

%bindata();

Thank you for your code, I'll be using this to complete my project, looks great.