What can you use to push through multiple values through a macro that runs through multiple dates?

[ChickenLittle]

Hi, I have a code that runs through each unique identifier ("account_id") in my DriverFile dataset and grabs the "Profit" from each monthly dataset table from Jan '22 ("202201") to Sep '22 ("202209") and adds it as a new variable for its related account_id (For example, my original data set only had the account_id, but now it also has Profit202201 Profit202202 Profit 202203 etc. for each account_id in my new dataset "FinalDataSet"). However, I need this this code to run over and over again for 50 different variables and not just Profit (i.e. I need it to do it also add it for GrossIncome, Expense, Allowances, etc.). Besides copying the code over and over again 50 times, is there a way to have it run through those 50 variables in some sort of %Let statement? Or maybe another embedded macro? Any help would be very much appreciated!!!  

data FinalDataSet;
set DriverFile (keep= account_id);

%macro loop1;
%do m=202201 %to 202209;
%if %substr(&m,5,2)=13 %then %let m=%eval(&m+88);

olderr=_error_;
set Table.MonthlyTable&m.( keep=account_id Profit
in=in1)
key=account_id;

Profit&m = 0;
if _iorc_=0 then do;
Profit&m=sum(0,Profit);

end;
else _error_=olderr;
%end;
%mend loop1;
%loop1;
run;

[ballardw · Accepted solution]

Suggestion: Provide a couple of example data sets that contain a few, like 3 to 5, of account_id values, 3 or 4 of the variables that you need to process and what the final result should be.

The description you give quite often means that you are forcing things manually that SAS provides such as BY group processing.

One thing that makes me very concerned: your data step that supposedly  creates Finaldataset will actually stop running when it encounters the %macro statement. NEVER define macros as part of other code. Create them separately and then call them, fine.

If this is supposed to create a sum (or other statistic such as mean, max, min or similar) of a variable then you would be much better off to use one of the Procedures like Proc Summary/Means if you really need a report or possibly Proc Report/Tabulate on a data set made by combining the ones you want.

Here is an example with three small data sets, 4 ids and 3 variables.

data set1;
   input id var1 var2 var3;
datalines;
1  15  20 .5
1  12  13 .7
2  1   1  1
2  2   2  1
3  22  22 22
3  11  11 11
3  44  44 44
;
data set2;
   input id var1 var2 var3;
datalines;
1  5  0 1.5
1  1  1 1.7
2  11   12  13
2  2.5  2.6  1.1
3  2.2  2.6 2.2
4  1.1  1.1 1.1
4  4  4 4
;
data set3;
   input id var1 var2 var3;
datalines;
1  1  2 .5
1  2  3  0
2  31 31  31
2  1.2 1.2 1.1
3  .22  .22 .22
3  .11  .11 .11
3  .44  .44 .44
;

data combined;
   set set1 set2 set3 indsname=indata;
   source=indata;
run;

/* data set*/
proc summary data=combined;
   class id source;
   var var1 var2 var3;
   output out=want sum= ;
run;

/* one type of report*/
proc tabulate data=combined;
  class id source;
  var var1 var2 var3;
  table source,
        id *(var1 var2 var3) * sum
   ;
run;

The data steps are to create something of known content. Combining the data uses the SET statement with multiple data sets, this will append the data (stack vertically) into one data set. The option INDSNAME= creates a temporary variable that holds the name of the data set that contributes each record you can use like any other variable. Temporary however means that SAS will drop the variable from the data set. In this case I just keep the value in a new variable that I named Source to indicate which data set each record comes from.

Proc summary with a class statement will create summaries of combinations of the class variables. The VAR variables are the ones that you request statistics for. In this case, following your example, I just asked for a Sum. However you can request any combination of the basic statistics, sum, mean, min, max, range, standard deviation, median, Q1 and Q3 (25 and 75 percentiles) and more all at one time. I picked proc summary as an example because it does multiple combinations of the class variables. If you look you will see a variable named _TYPE_ that corresponds to which level of summary is proviced: 0 = ALL the records, so we get the sum total across all of the sets,1 => the sum for each level of the Source data set or within each data set, 2=> each level of ID across all sets so you get the total for each id, 3=> the combinations of ID and Source, or the sum with each data set for each Id value. If all you wanted in a data set were the _type_=3 you could add the option NWAYS to the proc statement. The Output statement using SUM= without anything else says to name the output variables holding the sum the name they had from the Data= set. If you request multiple statistics you must provide some name rules. SAS makes one easy way by providing the option /AUTONAME which appends the statistic name to the variable name.

Proc tabulate is just one of the report procedures. But without knowing more about what you expect I won't try to customize a report.

Note: Generally I would attempt to add date information instead of a text value like WORK.SET1 to identify the records because there are many tools in SAS that work with date values. So with a date I could create summaries group data by ID and calendar quarter or year very easily.

[ChickenLittle]

Hi, this is great, thank you so much for your response, this is actually what I need despite having another result in mind. This is much more logical than what I had in mind. Thank you so much again for your super quick response!! I really appreciate it!!!

[Patrick]

@ChickenLittle  If what @ballardw proposed solves your problem then please mark it as solution. This not only rewards the author it also shows all of us that this question is answered.

[ChickenLittle]

Thanks for the heads up, I just accepted the solution.