DATA Step, Macro, Functions and more

Repetitively executing a Maco

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

Repetitively executing a Maco

[ Edited ]

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. 


Accepted Solutions
Solution
‎03-02-2018 05:46 AM
Frequent Contributor
Posts: 109

Re: Repetitively executing a Maco

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

 

A little modification to @KurtBremser 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.

View solution in original post


All Replies
Super User
Posts: 9,880

Re: Repetitively executing a Maco

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 65

Re: Repetitively executing a Maco

Posted in reply to KurtBremser

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.

Super User
Posts: 9,880

Re: Repetitively executing a Maco

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 65

Re: Repetitively executing a Maco

Posted in reply to KurtBremser

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.

Frequent Contributor
Posts: 109

Re: Repetitively executing a Maco

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

Re: Repetitively executing a Maco

Posted in reply to Satish_Parida

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.

Frequent Contributor
Posts: 109

Re: Repetitively executing a Maco

[ Edited ]

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;

 

Contributor
Posts: 65

Re: Repetitively executing a Maco

Posted in reply to Satish_Parida

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

Solution
‎03-02-2018 05:46 AM
Frequent Contributor
Posts: 109

Re: Repetitively executing a Maco

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

 

A little modification to @KurtBremser 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.

Contributor
Posts: 65

Re: Repetitively executing a Maco

Posted in reply to Satish_Parida

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 144 views
  • 0 likes
  • 3 in conversation