topic Re: complete a dataset with stadardized lines in New SAS User

complete a dataset with stadardized lines

PierreYvesILY — Thu, 16 Apr 2020 14:01:11 GMT

hello dear SAS experts,

I often have to add new lines such as the following ones to an existing dataset that has the same structure. I use the following code. The dataset Filialstruktur1 is completed by the dataset Filialstruktur_Ergaenzung.

From time to time, I have to add new datalines to the Filialstruktur_Ergaenzung dataset.

A dataline is based on 2 parameters for example here 91100 and 'FIL1':

MARKTREGION_BT = 'FIL1' ; NLHB_BT = 91100 ; NLBEZ_BT = 'FIL1' ; MBRHB_BT = 91100 ; MBRBEZ_BT_DASHB = 'FIL1' ; FILHB_BT = 91100 ; FILBEZ_BT_DASHB = 'FIL1' ; FILHB_BT_EC = '91100' ; output;

My question: how can I do better?

proc sort data=Filialstruktur1; by filhb_bt;run;

data Filialstruktur_Ergaenzung ;

length MARKTREGION_BT $35 NLHB_BT $5 NLBEZ_BT $35 MBRHB_BT $5 MBRBEZ_BT_DASHB $40 FILHB_BT $5 FILBEZ_BT_DASHB $40 ;

MARKTREGION_BT = 'FIL1' ; NLHB_BT = 91100 ; NLBEZ_BT = 'FIL1' ; MBRHB_BT = 91100 ; MBRBEZ_BT_DASHB = 'FIL1' ; FILHB_BT = 91100 ; FILBEZ_BT_DASHB = 'FIL1' ; FILHB_BT_EC = '91100' ; output;

MARKTREGION_BT = 'Vermittler' ; NLHB_BT = 99991 ; NLBEZ_BT = 'Vermittler' ; MBRHB_BT = 99991 ; MBRBEZ_BT_DASHB = 'Vermittler' ; FILHB_BT = 99991 ; FILBEZ_BT_DASHB = 'Vermittler' ; FILHB_BT_EC = '99991' ; output;

MARKTREGION_BT = 'FIL2' ; NLHB_BT = 99993 ; NLBEZ_BT = 'FIL2' ; MBRHB_BT = 99993 ; MBRBEZ_BT_DASHB = 'FIL2' ; FILHB_BT = 99993 ; FILBEZ_BT_DASHB = 'FIL2' ; FILHB_BT_EC = '99993' ; output;

MARKTREGION_BT = 'Online' ; NLHB_BT = 99992 ; NLBEZ_BT = 'Online' ; MBRHB_BT = 99992 ; MBRBEZ_BT_DASHB = 'Online' ; FILHB_BT = 99992 ; FILBEZ_BT_DASHB = 'Online' ; FILHB_BT_EC = '99992' ; output;

MARKTREGION_BT = 'FIL3' ; NLHB_BT = 99994 ; NLBEZ_BT = 'FIL3' ; MBRHB_BT = 99994 ; MBRBEZ_BT_DASHB = 'FIL3' ; FILHB_BT = 99994 ; FILBEZ_BT_DASHB = 'FIL3' ; FILHB_BT_EC = '99994' ; output;

MARKTREGION_BT = 'FIL4' ; NLHB_BT = 99995 ; NLBEZ_BT = 'FIL4' ; MBRHB_BT = 99995 ; MBRBEZ_BT_DASHB = 'FIL4' ; FILHB_BT = 99995 ; FILBEZ_BT_DASHB = 'FIL4' ; FILHB_BT_EC = '99995' ; output;

MARKTREGION_BT = 'Nicht_zustellbar' ; NLHB_BT = 99999 ; NLBEZ_BT = 'Nicht_zustellbar' ; MBRHB_BT = 99999 ; MBRBEZ_BT_DASHB = 'MBR Nicht zustellbar' ; FILHB_BT = 99999 ; FILBEZ_BT_DASHB = 'Nicht_zustellbar' ; FILHB_BT_EC = '99999' ; output;

run;

data Filialstruktur_Ergaenzt;

set Filialstruktur1 Filialstruktur_Ergaenzung;

run;

Re: complete a dataset with stadardized lines

ballardw — Thu, 16 Apr 2020 14:25:26 GMT

It is not clear whether you are adding the same items of if the things you need to add vary from time to time.

If it is always the same stuff then you could consider creating a permanent data set then append the data.

If the list changes from time to time but always needs the same items because of periodic "missing" you should build a data set of all the needed values. Then depending on some other issues, such as are these key values duplicated in the other set you could use a Merge to bring the data together with the standard set.

Re: complete a dataset with stadardized lines

Tom — Thu, 16 Apr 2020 14:28:54 GMT

You can just use INPUT statement and inline data (as long as it is NOT inside of a macro definition).

data Filialstruktur_Ergaenzung ;
  length MARKTREGION_BT $35 NLHB_BT $5 NLBEZ_BT $35 MBRHB_BT $5 
         MBRBEZ_BT_DASHB $40 FILHB_BT $5 FILBEZ_BT_DASHB $40 
  ;
  infile datalines dsd dlm='|' truncover;
  input MARKTREGION_BT -- FILBEZ_BT_DASHB ;
datalines4;
FIL1|91100|FIL1|91100|FIL1|91100|FIL1|91100
Vermittler|99991|Vermittler|99991|Vermittler|99991|Vermittler|99991
FIL2|99993|FIL2|99993|FIL2|99993|FIL2|99993
Online|99992|Online|99992|Online|99992|Online|99992
FIL3|99994|FIL3|99994|FIL3|99994|FIL3|99994
FIL4|99995|FIL4|99995|FIL4|99995|FIL4|99995
Nicht_zustellbar|99999|Nicht_zustellbar|99999|MBR Nicht zustellbar|99999|Nicht_zustellbar|99999
;;;;

If you want to make a dataset with similar structure to an existing dataset then instead of the LENGTH statement to define the variables you can use a SET statement reading the existing dataset. You can put the SET in an always false IF/THEN and it won't actually read any of the data, but it create the variables properly. Then you can just input using DATALINES (aka CARDS).

data Filialstruktur_Ergaenzung ;
  if 0 then set Filialstruktur1 ;
  infile datalines dsd dlm='|' truncover;
  input (_all_) (+0);
datalines4;
FIL1|91100|FIL1|91100|FIL1|91100|FIL1|91100
Vermittler|99991|Vermittler|99991|Vermittler|99991|Vermittler|99991
FIL2|99993|FIL2|99993|FIL2|99993|FIL2|99993
Online|99992|Online|99992|Online|99992|Online|99992
FIL3|99994|FIL3|99994|FIL3|99994|FIL3|99994
FIL4|99995|FIL4|99995|FIL4|99995|FIL4|99995
Nicht_zustellbar|99999|Nicht_zustellbar|99999|MBR Nicht zustellbar|99999|Nicht_zustellbar|99999
;;;;

You could even combine your two data steps.

data Filialstruktur_Ergaenzt;
  do until (eof);
    set Filialstruktur_Ergaenzung end=eof;
    output;
  end;
  infile datalines dsd dlm='|' truncover;
  do while(1=1);
    input MARKTREGION_BT -- FILBEZ_BT_DASHB ;
    output;
  end;
datalines4;
FIL1|91100|FIL1|91100|FIL1|91100|FIL1|91100
Vermittler|99991|Vermittler|99991|Vermittler|99991|Vermittler|99991
FIL2|99993|FIL2|99993|FIL2|99993|FIL2|99993
Online|99992|Online|99992|Online|99992|Online|99992
FIL3|99994|FIL3|99994|FIL3|99994|FIL3|99994
FIL4|99995|FIL4|99995|FIL4|99995|FIL4|99995
Nicht_zustellbar|99999|Nicht_zustellbar|99999|MBR Nicht zustellbar|99999|Nicht_zustellbar|99999
;;;;