generate a series of datasets in a loop and concatenate them in a single dataset

[PierreYvesILY]

dear SAS experts,

I have a source dataset FIL1 containing values of vaiables which names sart with ORB, DrK and NGS, and other vaiables related with year (Jahr) and month (Monat).

I want to calculate the mean of all ORB, DrK and NGS for each of the following periods per year :

- mean of the values for the period from all the months from month = 1 to &Monat_max.

- for instance, if Monat_max = 3, I want to calculate the mean values for the 3 periods:

• month 1, in a FIL2_1 dataset
• month 1 + 2, in a FIL3_2 dataset
• month 1+2+3. in a FIL3_3 dataset

Afterwards, I want to set all produced datasets in a single one FIL3.

I tried the following but I cannot make it work:

proc summary data=Dashboard_Primaerdaten nway;

class marktregion_bt nlbez_bt mbrbez_bt filbez_bt FILHB_BT jahr monat ;

var ORB: NGS: DrK: ;

output out=FIL1 sum=;

run;

* new ;

Data FIL3;

set _null_;

run;

data FIL3;

retain i 1;

if i <= &Monat_max. then do;

call execute (cats('proc means data=FIL1 Mean nway;

class marktregion_bt nlbez_bt mbrbez_bt filbez_bt FILHB_BT jahr / descending ;

var ORB: NGS: DrK:;

where Monat <=i;

output out=FIL3_i Mean=;

run;'));

Monat = 99||i;

i=i+1;

call execute (cats('data FIL3; set FIL3 FIL3_i; run;'))

end;

run;

how should I proceed? maybe my idea of the method is false...

regards

[PaigeMiller]

So, in my terminology, it sounds like you want a "year-to-date" calculation, given the values for 12 months.

Although it might be a bit of typing, I would use a multilabel format, and then you only need one dataset and you won't have to recombine the multiple data sets.

/* UNTESTED CODE */
proc format;
     value ytd (multilabel) 1='Jan' 1,2='Feb' 1,2,3='Mar' ... ; 
     /* I'm lazy, you type the rest */
run;

proc summary data=have;
    class month/mlf;
    var yourvariable;
    output out=want mean=;
    format month ytd.;
run;

[PierreYvesILY]

hello,

thanks for the answer. I used the recommanded method and get good results.

I Encounter nevertheless the following Problem:

* Berechnung der monatlichen Durchschnitte, MR Ebene ;

proc summary data=MR1 nway;

class marktregion_bt jahr monat/mlf;

var ORB: NGS: DrK: ;

output out=MR3 mean=;

format monat ytd. ;

run;

In the source dataset (MR1), the variable Jahr is numeric. In the result dataset, the Format has changed and the Jahr variable is Character. This is a major Problem for the operations coming next. Why is the Format of Jahr turned from numeric to Character? How can I avoid or correct this?

Regards

[PaigeMiller]

Are you talking about format types, or variable types?

Formats are numeric if you program them to be numeric. Formats are character if you program them to be character. How are you programming it?

Also, show us the log of the relevant parts of your program (with nothing chopped out, in the order that it appears in the log, unchanged). Please post the log as text by pasting it into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.

[PierreYvesILY]

this is the Code:

* Berechnung der monatlichen Summen, MR Ebene ;

proc summary data=Dashboard_Primaerdaten nway;

class marktregion_bt jahr monat ;

var ORB: NGS: DrK: ;

output out=MR1 sum=;

run;

data MR2 (drop=Monat);

set MR1;

format monat ytd.;

Monat_2 = cats(jahr,monat,'Somme');

run;

* Berechnung der monatlichen Durchschnitte, MR Ebene ;

proc summary data=MR1 nway;

class marktregion_bt jahr monat/mlf;

var ORB: NGS: DrK: ;

output out=MR3 mean=;

format monat ytd. ;

run;

data MR3 (drop=Monat);

set MR3;

Monat_2 = cats(jahr,monat,'Moy');

run;

* Berechnung der jährlichen Summen, MR Ebene ;

proc summary data=Dashboard_Primaerdaten nway;

class marktregion_bt jahr;

var ORB: NGS: DrK: ;

output out=MR4 sum=;

run;

data MR4;

set MR4;

Monat_2 = cats(jahr,'Jahr');

run;

* Zusammenlegung der Datasets, MR Ebene ;

data MR (drop=_TYPE_ _FREQ_);

length STUFE $9 STRUKNNZ $8;

set MR2 MR3 MR4;

STUFE = 'MR';

struknnz = 'MRX'||MARKTREGION_BT;

run;

proc sort data=mr; by struknnz; run;

this is the log:

1                                                          The SAS System                         09:05 Wednesday, February 10, 2021

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Programm';
4          %LET _CLIENTPROCESSFLOWNAME='Prozessfluss';
5          %LET _CLIENTPROJECTPATH='/pkkrp/200_Entwicklung/10_PYI/Entw_DashBoard_v14_3_2021.egp';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='Entw_DashBoard_v14_3_2021.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASBIHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         * Berechnung der monatlichen Summen, MR Ebene ;
27         proc summary data=Dashboard_Primaerdaten nway;
28         class marktregion_bt jahr monat ;
29         var  ORB: NGS: DrK: ;
30         output out=MR1 sum=;
31         run;

NOTE: There were 33430 observations read from the data set WORK.DASHBOARD_PRIMAERDATEN.
NOTE: The data set WORK.MR1 has 390 observations and 21 variables.
NOTE: Compressing data set WORK.MR1 decreased size by 0.00 percent. 
      Compressed is 2 pages; un-compressed would require 2 pages.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           0.14 seconds
      cpu time            0.07 seconds
      

32         data MR2 (drop=Monat);
33         set MR1;
34         format monat ytd.;
35         Monat_2 = cats(jahr,monat,'Somme');
36         run;

NOTE: There were 390 observations read from the data set WORK.MR1.
NOTE: The data set WORK.MR2 has 390 observations and 21 variables.
NOTE: Compressing data set WORK.MR2 decreased size by 33.33 percent. 
      Compressed is 2 pages; un-compressed would require 3 pages.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
      

37         
2                                                          The SAS System                         09:05 Wednesday, February 10, 2021

38         * Berechnung der monatlichen Durchschnitte, MR Ebene ;
39         proc summary data=MR1 nway;
40             class marktregion_bt jahr monat/mlf;
41             var ORB: NGS: DrK: ;
42             output out=MR3 mean=;
43             format monat ytd. ;
44         run;

NOTE: There were 390 observations read from the data set WORK.MR1.
NOTE: The data set WORK.MR3 has 516 observations and 21 variables.
NOTE: Compressing data set WORK.MR3 increased size by 50.00 percent. 
      Compressed is 3 pages; un-compressed would require 2 pages.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           0.04 seconds
      cpu time            0.05 seconds
      

45         data MR3 (drop=Monat);
46         set MR3;
47         Monat_2 = cats(jahr,monat,'Moy');
48         run;

NOTE: There were 516 observations read from the data set WORK.MR3.
NOTE: The data set WORK.MR3 has 516 observations and 21 variables.
NOTE: Compressing data set WORK.MR3 decreased size by 25.00 percent. 
      Compressed is 3 pages; un-compressed would require 4 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

49         
50         * Berechnung der jährlichen Summen, MR Ebene ;
51         proc summary data=Dashboard_Primaerdaten nway;
52         class marktregion_bt jahr;
53         var  ORB: NGS: DrK: ;
54         output out=MR4 sum=;
55         run;

NOTE: There were 33430 observations read from the data set WORK.DASHBOARD_PRIMAERDATEN.
NOTE: The data set WORK.MR4 has 45 observations and 20 variables.
NOTE: Compressing data set WORK.MR4 increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: PROCEDURE SUMMARY used (Total process time):
      real time           0.03 seconds
      cpu time            0.06 seconds
      

56         data MR4;
57         set MR4;
58         Monat_2 = cats(jahr,'Jahr');
59         run;

NOTE: There were 45 observations read from the data set WORK.MR4.
NOTE: The data set WORK.MR4 has 45 observations and 21 variables.
NOTE: Compressing data set WORK.MR4 increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
3                                                          The SAS System                         09:05 Wednesday, February 10, 2021

      real time           0.00 seconds
      cpu time            0.00 seconds
      

60         
61         * Zusammenlegung der Datasets, MR Ebene ;
62         data MR (drop=_TYPE_ _FREQ_);
63         length STUFE $9 STRUKNNZ $8;
64         set MR2 MR3 MR4;
ERROR: Variable JAHR has been defined as both character and numeric.
65         STUFE = 'MR';
66         struknnz = 'MRX'||MARKTREGION_BT;
67         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.MR may be incomplete.  When this step was stopped there were 0 observations and 21 variables.
WARNING: Data set WORK.MR was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

68         proc sort data=mr; by struknnz; run;

NOTE: Input data set is already sorted, no sorting done.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

69         
70         GOPTIONS NOACCESSIBLE;
71         %LET _CLIENTTASKLABEL=;
72         %LET _CLIENTPROCESSFLOWNAME=;
73         %LET _CLIENTPROJECTPATH=;
74         %LET _CLIENTPROJECTPATHHOST=;
75         %LET _CLIENTPROJECTNAME=;
76         %LET _SASPROGRAMFILE=;
77         %LET _SASPROGRAMFILEHOST=;
78         
79         ;*';*";*/;quit;run;
80         ODS _ALL_ CLOSE;
81         
82         
83         QUIT; RUN;
84         

[PaigeMiller · Accepted solution]

Try this:

proc summary data=MR1 nway;
    class marktregion_bt jahr;
    class monat/mlf;
    var ORB: NGS: DrK: ;
    output out=MR3 mean=;
    format monat ytd. ;
run;

[PierreYvesILY]

thanks a lot, it solved the problem.

I didn't know that it was allowed to use more that one 'class'.

thx again, and have a nice day

regards

PY

[PierreYvesILY]

as format definition I taped:

* Neues Format für die Berechnung der Durchschnitte für n Monate ;
proc format;
value ytd (multilabel) 
1							='Jan'
1,2							='Feb' 
1,2,3						='Mar'
1,2,3,4						='Apr'
1,2,3,4,5					='Mai'
1,2,3,4,5,6					='Jun'
1,2,3,4,5,6,7				='Jul'
1,2,3,4,5,6,7,8				='Aug'
1,2,3,4,5,6,7,8,9			='Sep'
1,2,3,4,5,6,7,8,9,10		='Oct'
1,2,3,4,5,6,7,8,9,10,11		='Nov'
1,2,3,4,5,6,7,8,9,10,11,12	='Dec'
; 
run;

[Reeza]

And if you're doing moving averages and have SAS/ETS licensed PROC EXPAND may be a better solution.

FYI - each call of your macro creates a data set called FIL3_i - put i is fixed. So each data set output has the same name and isn't dynamic and therefore you're left with only the last data set, you don't have one for each call execute. You need to put that statement more dynamically so that the I carries the variable value of I but at present SAS is treating your I as text or an unknown variable. I would assume you're getting errors unless you have a variable named I in your data set FIL1?

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

There are typically two ways of aggregating data as you go, one is to append at the end of each loop and the second is to save all the outputs and stack them at the end. This example demonstrates the approach of adding it in during the loop: https://gist.github.com/statgeek/353374a5d8ea4f0c89ce5d80a47f4a4c

To add them all after, once you have the I issue sorted out you can do the following, using the colon operator:

data want;
set FIL3_: ;
run;