Simplify data step

[PierreYvesILY]

hello dear SAS experts,

I have this data step in a new programm:

data RKR_MBR_Tag_2 (drop= _: NIEDERLASSUNG_BT_EC MARKTBEREICH_BT_EC);

merge CBR_TGL_MBR (in=a drop=monat kw tag)

CBR_KW_MBR (in=b drop=monat kw)

CBR_Monat_MBR (in=c drop=monat)

CBR_Jahr_MBR (in=d)

Offene_Filialen_t_mbr (in=e)

ORB_Daten_t_mbr (in=f drop=monat kw tag)

ORB_Daten_k_mbr (in=g drop=kw)

ORB_Daten_m_mbr (in=h drop=monat)

ORB_Daten_j_mbr (in=i);

by MR_KURZ_BT NIEDERLASSUNGBEZ_BT_EC MARKTBEREICHBEZ_BT_EC;

if (Offene_Filialen - Anzahl_aktive_FIL_Tag) > 0 then Anzahl_inaktive_FIL_Tag = Offene_Filialen - Anzahl_aktive_FIL_Tag;

else Anzahl_inaktive_FIL_Tag = 0;

if (Offene_Filialen - Anzahl_aktive_FIL_Jahr) > 0 then Anzahl_inaktive_FIL_Jahr = Offene_Filialen - Anzahl_aktive_FIL_Jahr;

else Anzahl_inaktive_FIL_Jahr = 0;

if (Offene_Filialen - Anzahl_aktive_FIL_Monat)> 0 then Anzahl_inaktive_FIL_Monat = Offene_Filialen - Anzahl_aktive_FIL_Monat;

else Anzahl_inaktive_FIL_Monat = 0;

if (Offene_Filialen - Anzahl_aktive_FIL_KW) > 0 then Anzahl_inaktive_FIL_KW = Offene_Filialen - Anzahl_aktive_FIL_KW;

else Anzahl_inaktive_FIL_KW = 0;

if NIEDERLASSUNGBEZ_BT_EC not in ('DrKlein', 'P7S1', 'SMAVA' , 'Direktvertrieb', 'Online', 'Swiss_Life', 'Sonstige_ZE', 'Nicht_zustellbar');

NGV_Vortag = coalesce(NGV_Vortag,0);

Vortag_Anzahl_RKR = coalesce(Vortag_Anzahl_RKR,0);

Anzahl_aktive_FIL_Tag = coalesce(Anzahl_aktive_FIL_Tag,0);

NGV_Woche = coalesce(NGV_Woche,0);

Woche_Anzahl_RKR = coalesce(Woche_Anzahl_RKR,0);

Anzahl_aktive_FIL_KW = coalesce(Anzahl_aktive_FIL_KW,0);

NGV_Monat = coalesce(NGV_Monat,0);

Monat_Anzahl_RKR = coalesce(Monat_Anzahl_RKR,0);

Anzahl_aktive_FIL_Monat = coalesce(Anzahl_aktive_FIL_Monat,0);

NGV_Jahr = coalesce(NGV_Jahr,0);

Jahr_Anzahl_RKR = coalesce(Jahr_Anzahl_RKR,0);

Anzahl_aktive_FIL_Jahr = coalesce(Anzahl_aktive_FIL_Jahr,0);

Offene_Filialen = coalesce(Offene_Filialen,0);

Antraege_Vortag = coalesce(Antraege_Vortag,0);

Antraege_KW = coalesce(Antraege_KW,0);

Antraege_Monat = coalesce(Antraege_Monat,0);

Antraege_Jahr = coalesce(Antraege_Jahr,0);

Anzahl_inaktive_FIL_Tag = coalesce(Anzahl_inaktive_FIL_Tag,0);

Anzahl_inaktive_FIL_Jahr =coalesce(Anzahl_inaktive_FIL_Jahr,0);

Anzahl_inaktive_FIL_Monat =coalesce(Anzahl_inaktive_FIL_Monat,0);

Anzahl_inaktive_FIL_KW =coalesce(Anzahl_inaktive_FIL_KW,0);

run;

1) the coalesce formel is used to get rid of missing values and set them at 0.

2) all the variables used in the coalesce are numeric

question:

1) how can I store all the variable names in a macro variable and use it in a loop to set all missing values to 0 ?

2) how can I replace the if - then - else at the beginning with a more efficient procedure? can I store the names jahr - monat - tag - kw in a macrovariable and then use them in a loop or something else to execute the formal 4 times, and how?

3) do you have better ideas?

thanks in advance

regards

PY

[ballardw · Accepted solution]

---

@PierreYvesILY wrote:

hello dear SAS experts,

I have this data step in a new programm:

data RKR_MBR_Tag_2 (drop= _: NIEDERLASSUNG_BT_EC MARKTBEREICH_BT_EC);

merge CBR_TGL_MBR (in=a drop=monat kw tag)

CBR_KW_MBR (in=b drop=monat kw)

CBR_Monat_MBR (in=c drop=monat)

CBR_Jahr_MBR (in=d)

Offene_Filialen_t_mbr (in=e)

ORB_Daten_t_mbr (in=f drop=monat kw tag)

ORB_Daten_k_mbr (in=g drop=kw)

ORB_Daten_m_mbr (in=h drop=monat)

ORB_Daten_j_mbr (in=i);

by MR_KURZ_BT NIEDERLASSUNGBEZ_BT_EC MARKTBEREICHBEZ_BT_EC;

if (Offene_Filialen - Anzahl_aktive_FIL_Tag) > 0 then Anzahl_inaktive_FIL_Tag = Offene_Filialen - Anzahl_aktive_FIL_Tag;

else Anzahl_inaktive_FIL_Tag = 0;

if (Offene_Filialen - Anzahl_aktive_FIL_Jahr) > 0 then Anzahl_inaktive_FIL_Jahr = Offene_Filialen - Anzahl_aktive_FIL_Jahr;

else Anzahl_inaktive_FIL_Jahr = 0;

if (Offene_Filialen - Anzahl_aktive_FIL_Monat)> 0 then Anzahl_inaktive_FIL_Monat = Offene_Filialen - Anzahl_aktive_FIL_Monat;

else Anzahl_inaktive_FIL_Monat = 0;

if (Offene_Filialen - Anzahl_aktive_FIL_KW) > 0 then Anzahl_inaktive_FIL_KW = Offene_Filialen - Anzahl_aktive_FIL_KW;

else Anzahl_inaktive_FIL_KW = 0;

if NIEDERLASSUNGBEZ_BT_EC not in ('DrKlein', 'P7S1', 'SMAVA' , 'Direktvertrieb', 'Online', 'Swiss_Life', 'Sonstige_ZE', 'Nicht_zustellbar');

NGV_Vortag = coalesce(NGV_Vortag,0);

Vortag_Anzahl_RKR = coalesce(Vortag_Anzahl_RKR,0);

Anzahl_aktive_FIL_Tag = coalesce(Anzahl_aktive_FIL_Tag,0);

NGV_Woche = coalesce(NGV_Woche,0);

Woche_Anzahl_RKR = coalesce(Woche_Anzahl_RKR,0);

Anzahl_aktive_FIL_KW = coalesce(Anzahl_aktive_FIL_KW,0);

NGV_Monat = coalesce(NGV_Monat,0);

Monat_Anzahl_RKR = coalesce(Monat_Anzahl_RKR,0);

Anzahl_aktive_FIL_Monat = coalesce(Anzahl_aktive_FIL_Monat,0);

NGV_Jahr = coalesce(NGV_Jahr,0);

Jahr_Anzahl_RKR = coalesce(Jahr_Anzahl_RKR,0);

Anzahl_aktive_FIL_Jahr = coalesce(Anzahl_aktive_FIL_Jahr,0);

Offene_Filialen = coalesce(Offene_Filialen,0);

Antraege_Vortag = coalesce(Antraege_Vortag,0);

Antraege_KW = coalesce(Antraege_KW,0);

Antraege_Monat = coalesce(Antraege_Monat,0);

Antraege_Jahr = coalesce(Antraege_Jahr,0);

Anzahl_inaktive_FIL_Tag = coalesce(Anzahl_inaktive_FIL_Tag,0);

Anzahl_inaktive_FIL_Jahr =coalesce(Anzahl_inaktive_FIL_Jahr,0);

Anzahl_inaktive_FIL_Monat =coalesce(Anzahl_inaktive_FIL_Monat,0);

Anzahl_inaktive_FIL_KW =coalesce(Anzahl_inaktive_FIL_KW,0);

run;

1) the coalesce formel is used to get rid of missing values and set them at 0.

2) all the variables used in the coalesce are numeric

question:

1) how can I store all the variable names in a macro variable and use it in a loop to set all missing values to 0 ?

2) how can I replace the if - then - else at the beginning with a more efficient procedure? can I store the names jahr - monat - tag - kw in a macrovariable and then use them in a loop or something else to execute the formal 4 times, and how?

3) do you have better ideas?

thanks in advance

regards

PY

---

No macro coding needed. When you want to do the same thing to multiple variables the approach is to use an array and loop over the elements (variables in an array). The example below creates a small example data set with some missing values. Then the second data step uses an array to set the missing values to 0.

data have;
   input x y z;
datalines;
1 . 2 
1 2 3
. 5 .
. . .
;

data example;
   set have;
   /* create an array named A with the variables
      x y z as elements*/
   array a {*} x y z;
   do i=1 to dim(a);
      if missing(a[i]) then a[i]=0;
   end;
   drop i;
run;

The Dim function returns the number of elements in an array. You can reference the specific element in an array using arrayname(integer expression). The integer expression needs to return a value in the range of the number of elements for a basic array with 1 to n elements. You can use different brackets to enclose the expression. I find the [ ] makes it easier to tell that I am using an array element quickly. The {*} tells SAS that a list of variables follows.

Note: you can create new variables with: Array newvarname {4} ; creating Newvarname1, Newvarname2, Newvarname3 and Newvarname4.

Basic array caveats: All elements of an array must be of the same type, either all numeric or all character.

The name of an array cannot duplicate the name of an existing variable.

To do parallel processing involving multiple variables you create an array for each of the similar variables, order and spelling count, and loop over them. Pseudo code for something similar to what you do:

array act {*}  fil_tag fil_jahr fil_monat fil_kw;
array inact {*}  _inact_fil_tag _inact_fil_jahr _inact_fil_monat _inact_fil_kw;

do i= 1 to dim(act);
   if (offene_filialen - act[i] > 0 then inact[i] = offene_filialen - act[i];
   else inact[i] = 0;
end;

You can also use an array in functions like sum.  Result = sum (of arrayname(*)); which uses all of the elements of an array as arguments in the Sum function.

[PierreYvesILY]

thx, I could reach my Goals thanks to your explanations. It worked perfectly.