BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PierreYvesILY
Pyrite | Level 9

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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
;;;;

View solution in original post

2 REPLIES 2
ballardw
Super User

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.

 

 

Tom
Super User Tom
Super User

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
;;;;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 508 views
  • 1 like
  • 3 in conversation