Solved: Re: processing a variable number of programm blocs after one another

PierreYvesILY · Posted 07-15-2019 06:27 AM

hi,

I use a long programm to download, process, and export data, on a monthly basis. I want to use this programm several times to cover several months, one after the other, and export a combined file gathering all monthly data.

For instance, I now have a result : month n / structure / data 1..x

Target is:

month n / structure / data 1..x

month n+1 / structure / data 1..x

...

month n+i / structure / data 1..x, i being the number of selected months.

How can I achieve this?

ballardw · Posted 07-16-2019 05:55 PM

@PierreYvesILY wrote:

hello,

thanks for your answer.

1. The names of the variables with &Jahr11. and so forth are created exactly for the purpose of copying the results in separate columns in Excel afterwards.

2. I have to calculate evolutions in % between both years, so I build yearly variables to do so : (Var&Jahr11.- Var &Jahr12.)=Delta &Jahr11. in %

If you can suggest a way to calculate the Delta with only one variable + an additional variable Jahr, I'd be pleased to use it.

It would simplify the code a LOT.

I don't understand what you mean by: proc summary and a data step with array code

thanks and have a nice day

PY

Would have to see some example INPUT data to see what is possible.

Proc summary (or means) will group data and do sums (or other statistics) into the same variable with less fuss than SQL.

proc summary data=  Liste_teil_5 nway;
   class STUFE MARKTREGION_BT NL_BT NLBEZ_BT MBRBEZ_BT;
   var  ORB_Anzahl_Akt_&Jahr11.  ORB_TVOL_Akt_&Jahr11.
        ORB_Anzahl_Akt_&Jahr11.    /* continue with all the 
                                   variables that need sums*/
   ;
   output out =temp  sum= ;
run;

If it happens that you need every single one of your variables that start with ORB to be summed the above could reduce the VAR statement to something like:

var ORB: ;

as proc summary/ means can use variable lists.

proc summary data=  Liste_teil_5 nway;
   class STUFE MARKTREGION_BT NL_BT NLBEZ_BT MBRBEZ_BT;
   var  ORB: ngs: ;  
   output out =temp  sum= ;
run;

The above would sum ALL numeric variables whose names start with ORB and NGS and place the sum in a variable of the same name in the output set.

Then a data step similar to:

data want;
   set temp;
   /* calculate the rates*/
   array n <list the numerator variables>;
   array d <list the denominator variables in corresponding order>;
   array r <list the results variables of the rate calculations>;
   /* this do loop */
   do i= 1 to dim(n);
      r[i] = n[i] / d[i];
   end;
   /* would replace all of these */
   ORB_Anzahl_VZK_&Jahr11. =  ORB_Anzahl_Akt_&Jahr11./Anzahl_VZK_&Jahr11.;

   format orb: NGS: commax17.;
run;

I wasn't about to copy out all of your variable names.

The ability to use variable lists in data step code can make things much simpler. If you need to process columns that appear in order you can use a list with two dashes in an array definition (or the var statement in Proc Summary / means).

Example: if the variables ABC ABD ABQ BDD BDE are adjacent and I want to process them in some way in that order I could use an array definition of : array v ABC -- BDE;

Also not the variable list in the Format statement much simpler. Or if I had a lot of variables to assign a format I might be tempted to use Proc Datasets after the set is created to assign the format instead of having so many format commax17. scattered through the code.

View solution in original post

PaigeMiller · Posted 07-15-2019 07:01 AM

Why would month N have to be processed before month N+1? Can't you just process all the months together and then export one file?

--
Paige Miller

PierreYvesILY · Posted 07-15-2019 07:17 AM

No, it would be a cumulative File as a result, which I already process also.

The program selects data for a certain time period, and then for each variable the data are summed, or other calculations take place.

The programm has 3 main blocs where the data downloaded form diffrent sources are processed this way. So, I have for each block to select the data of the 1st month, then process, then store the results. Afterwards, I want to do the same for the next month.

After all this, I need to merge all tables obtained and process the export.

My goal is to edit i monthly data stands for each structure of the company.

i.e: if i=6 months => 6 lines, and each line having the result of each variable for each month.

I'm not familiar with SAS or programming, I first started to learn SAS programming in August last year.

Thanks for your help

PaigeMiller · Posted 07-15-2019 07:59 AM

Nothing you have said, if I am understanding you properly, makes it necessary to perform the step separaetly for each month. SAS has many ways to do one big analysis, BY MONTH, so you don't have the run the code many times; you run it once and let SAS break it up.

--
Paige Miller

PierreYvesILY · Posted 07-15-2019 08:13 AM

I've no idea how to do this with the data I have (month has to be added to the data), and break it correctly down afterwards.

For instance, I have this step, that has to be run for each month and for each level of the company structure (the levels are quoted in the group option below):

/* Aggregation und Quoten-Neu Berechnung für FIL */

proc sql;

create table FIL2 as

select STUFE ,

MARKTREGION_BT,

NL_BT,

NLBEZ_BT,

MBRBEZ_BT,

/* '' as FILHB_BT length=5, */

FILBEZ_BT length=35,

sum(ORB_Anzahl_Akt_&Jahr11.) as ORB_Anzahl_Akt_&Jahr11. format commax17.,

sum(ORB_TVOL_Akt_&Jahr11.) as ORB_TVOL_Akt_&Jahr11. format commax17.,

sum(ORB_Anzahl_Akt_&Jahr11.) / sum(Anzahl_VZK_&Jahr11.) as ORB_Anzahl_VZK_&Jahr11. format commax17.1,

sum(ORB_Anzahl_Gen_&Jahr11.) as ORB_Anzahl_Gen_&Jahr11. format commax17.,

sum(ORB_TVOL_Gen_&Jahr11.) as ORB_TVOL_Gen_&Jahr11. format commax17.,

sum(ORB_Anzahl_Gen_&Jahr11.) / sum(ORB_Anzahl_Akt_&Jahr11.) as ORB_GEN_Quote_&Jahr11. format commax17.3,

(case

when (sum(ORB_kein_Fino_&Jahr11.) = 0 and sum(ORB_Anzahl_Akt_&Jahr11.) not in (0,.)) then 1

when (sum(ORB_kein_Fino_&Jahr11.) = . and sum(ORB_Anzahl_Akt_&Jahr11.) not in (0,.)) then 1

else (1 - (sum(ORB_kein_Fino_&Jahr11.) / sum(ORB_Anzahl_Akt_&Jahr11.)))

end)

as ORB_FINO_QUOTE_&Jahr11. format commax17.3,

sum(NGS_Anzahl_&Jahr11.) as NGS_Anzahl_&Jahr11. format commax17.,

sum(NGS_NGV_&Jahr11.) as NGS_NGV_&Jahr11. format commax17.,

sum(NGS_NGV_&Jahr11.) / sum(NGS_Anzahl_&Jahr11.) as NGS_TICKET_AVG_&Jahr11. format commax17.1,

sum(NGS_RSV_Anz_&Jahr11.) / sum(NGS_Anzahl_&Jahr11.) as RSV_QUOTE_&Jahr11. format commax17.3,

sum(NGS_Anzahl_&Jahr11.) / sum(ORB_Anzahl_Gen_&Jahr11.) as NGS_Auszahlungsquote_&Jahr11. format commax17.3,

sum(Anzahl_VZK_&Jahr11.) as Anzahl_VZK_&Jahr11. format commax17.2,

sum(ORB_kein_Fino_&Jahr11.) as ORB_kein_Fino_&Jahr11. format commax17.,

sum(NGS_RSV_Anz_&Jahr11.) as NGS_RSV_Anz_&Jahr11. format commax17.,

sum(ORB_Anzahl_Akt_Online_&Jahr11.) as ORB_Anzahl_Akt_Online_&Jahr11. format commax17.,

sum(ORB_TVOL_Akt_Online_&Jahr11.) as ORB_TVOL_Akt_Online_&Jahr11. format commax17.,

sum(ORB_Anzahl_Gen_Online_&Jahr11.) as ORB_Anzahl_Gen_Online_&Jahr11. format commax17.,

sum(ORB_TVOL_Gen_Online_&Jahr11.) as ORB_TVOL_Gen_Online_&Jahr11. format commax17.,

sum(NGS_Anzahl_Online_&Jahr11.) as NGS_Anzahl_Online_&Jahr11. format commax17.,

sum(NGS_NGV_Online_&Jahr11.) as NGS_NGV_Online_&Jahr11. format commax17.,

sum(NGS_Anzahl_Online_&Jahr11.) / sum(ORB_Anzahl_Gen_Online_&Jahr11.) as NGS_Auszahlungsquote_Online_&Jahr11. format commax17.3,

sum(NGS_Anzahl_&Jahr11.) / sum(Anzahl_VZK_&Jahr11.) as NGS_Anzahl_VZK_&Jahr11. format commax17.1,

sum(ORB_Anzahl_Gruen_&Jahr11.) / sum(ORB_Anzahl_Gen_&Jahr11.) as ORB_GRUEN_Quote_&Jahr11. format commax17.3,

sum(ORB_Anzahl_Akt_&Jahr12.) as ORB_Anzahl_Akt_&Jahr12. format commax17.,

sum(ORB_TVOL_Akt_&Jahr12.) as ORB_TVOL_Akt_&Jahr12. format commax17.,

sum(ORB_Anzahl_Akt_&Jahr12.) / sum(Anzahl_VZK_&Jahr12.) as ORB_Anzahl_VZK_&Jahr12. format commax17.1,

sum(ORB_Anzahl_Gen_&Jahr12.) as ORB_Anzahl_Gen_&Jahr12. format commax17.,

sum(ORB_TVOL_Gen_&Jahr12.) as ORB_TVOL_Gen_&Jahr12. format commax17.,

sum(ORB_Anzahl_Gen_&Jahr12.) / sum(ORB_Anzahl_Akt_&Jahr12.) as ORB_GEN_Quote_&Jahr12. format commax17.3,

(case

when (sum(ORB_kein_Fino_&Jahr12.) = 0 and sum(ORB_Anzahl_Akt_&Jahr12.) not in (0,.)) then 1

when (sum(ORB_kein_Fino_&Jahr12.) = . and sum(ORB_Anzahl_Akt_&Jahr12.) not in (0,.)) then 1

else (1 - (sum(ORB_kein_Fino_&Jahr12.) / sum(ORB_Anzahl_Akt_&Jahr12.)))

end)

as ORB_FINO_QUOTE_&Jahr12. format commax17.3,

sum(NGS_Anzahl_&Jahr12.) as NGS_Anzahl_&Jahr12. format commax17.,

sum(NGS_NGV_&Jahr12.) as NGS_NGV_&Jahr12. format commax17.,

sum(NGS_NGV_&Jahr12.) / sum(NGS_Anzahl_&Jahr12.) as NGS_TICKET_AVG_&Jahr12. format commax17.1,

sum(NGS_RSV_Anz_&Jahr12.) / sum(NGS_Anzahl_&Jahr12.) as RSV_QUOTE_&Jahr12. format commax17.3,

sum(NGS_Anzahl_&Jahr12.) / sum(ORB_Anzahl_Gen_&Jahr12.) as NGS_Auszahlungsquote_&Jahr12. format commax17.3,

sum(Anzahl_VZK_&Jahr12.) as Anzahl_VZK_&Jahr12. format commax17.2,

sum(ORB_kein_Fino_&Jahr12.) as ORB_kein_Fino_&Jahr12. format commax17.,

sum(NGS_RSV_Anz_&Jahr12.) as NGS_RSV_Anz_&Jahr12. format commax17.,

sum(ORB_Anzahl_Akt_Online_&Jahr12.) as ORB_Anzahl_Akt_Online_&Jahr12. format commax17.,

sum(ORB_TVOL_Akt_Online_&Jahr12.) as ORB_TVOL_Akt_Online_&Jahr12. format commax17.,

sum(ORB_Anzahl_Gen_Online_&Jahr12.) as ORB_Anzahl_Gen_Online_&Jahr12. format commax17.,

sum(ORB_TVOL_Gen_Online_&Jahr12.) as ORB_TVOL_Gen_Online_&Jahr12. format commax17.,

sum(NGS_Anzahl_Online_&Jahr12.) as NGS_Anzahl_Online_&Jahr12. format commax17.,

sum(NGS_NGV_Online_&Jahr12.) as NGS_NGV_Online_&Jahr12. format commax17.,

sum(NGS_Anzahl_Online_&Jahr12.) / sum(ORB_Anzahl_Gen_Online_&Jahr12.) as NGS_Auszahlungsquote_Online_&Jahr12. format commax17.3,

sum(NGS_Anzahl_&Jahr12.) / sum(Anzahl_VZK_&Jahr12.) as NGS_Anzahl_VZK_&Jahr12. format commax17.1,

sum(ORB_Anzahl_Gruen_&Jahr12.) / sum(ORB_Anzahl_Gen_&Jahr12.) as ORB_GRUEN_Quote_&Jahr12. format commax17.3

from Liste_teil_5

group by

STUFE,

MARKTREGION_BT,

NL_BT,

NLBEZ_BT,

MBRBEZ_BT,

FILBEZ_BT;

quit;

in the next step, I take the next level:

/* Aggregation und Quoten-Neu Berechnung für MBR */

proc sql;

create table MBR as

select 'MBR' as STUFE ,

MARKTREGION_BT,

NL_BT,

NLBEZ_BT,

MBRBEZ_BT,

/* '' as FILHB_BT length=5, */

'' as FILBEZ_BT length=35,