Search code examples
macrossasdatasetaddition

Add a variable (column) in data set (SAS)

I can't find the solution for this simple problem: I want to add a colum/variable in my data set. This variable will always have the same value, stored in the macro variable &value. And I am in a macro so I don't know if it change anything... This is the step before merging 2 data step. So far, here's what I have:

%do i=1 %to 10;
    data &new_data_set;
        set &new_data_set;
        Nom_controle=&Nom_Controle;
        Partenaire=&Partenaire;
    run;
%end;

I'm trying to add to my data-set (which was previously defined in the macro as &new_data_set) a column/variable named "Nom_Controle" which always takes the value stored in the macro variable &Nom_controle (previously defined too). I'm also trying to add a second column/variable named "Partenaire" which always takes the value stored in the macro variable &Partenaire (previously defined too). Of course, as I'm posting here, my code doesn't work. Can you help me?

EDIT: after some ask me to in order to help me, here is the macro this code is from (the full thing):

%macro presence_mouvement (data_set_detail_mouvement, data_set_mouvement);
    %if %sysfunc(exist(&data_set_mouvement)) AND %sysfunc(exist(&data_set_detail_mouvement)) %then %do; *Check if my data set actually exist;
        %let suffix=_2;
        %let new_data_set=&data_set_detail_mouvement&suffix; *Create the name of the new data set I'm going to save the result of the next proc sql in;
        proc SQL noprint; *Proc to look for errors in a previous data set and print it in the new data set;
                create table &new_data_set as
                insert into &new_data_set 
                SELECT num_mouvement 
                FROM &data_set_detail_mouvement
                EXCEPT 
                    SELECT num_mouvement
                    FROM &data_set_mouvement);
            
        %let Nom_controle=Presence_mouvement; *Creation of a new variable;
        %if %sysfunc(length(&data_set_detail_mouvement))=29 %then %do; *Creation of a second variable (value conditional to the size of a previous variable);
            %let Partenaire=%sysfunc(substr(&data_set_detail_mouvement, 9, 3)); %end;
        %else %if %sysfunc(length(&data_set_detail_mouvement))=30 %then %do; 
            %let Partenaire=%sysfunc(substr(&data_set_detail_mouvement, 9, 4)); %end;
        %else %do;
            %let Partenaire=%sysfunc(substr(&data_set_detail_mouvement, 9, 6)); %end;

        %do i=1 %to 10;
            data &new_data_set;
                set &new_data_set;
                Nom_controle=&Nom_Controle;
                Partenaire=&Partenaire;
            run;
        %end; 
    %end;*End of the actions to do in case the two data set in parameters exist;

%else %do; *Actions to do in case the two data set in parameters don't exist;
  data _null_;
     file print;
     put #3 @10 "At least one of the data set does not exist";
  run;
%end;
*This macro is aiming at pointing error in a previous data set, print them in a new data set and add two new variables/columns to this new data set (indicating their origin). The next set is going to be to merge this new data set to another one;
%mend presence_mouvement;

%presence_mouvement (sasuser.bgpi__detail_mouvement, sasuser.bgpi__mouvement);

I also wanted to say that I tested the rest of the macro before trying to add new variable so the rest of the macro shouldn't have any problem. But who knows...

Solution

  • Run a single data step, setting the new variables to the values setup in macro variables. If the values setup are character in nature the data step variables need to resolve those macro variables within double quotes.

    data &new_data_set;
  set &new_data_set;
  retain 
    Nom_controle "&Nom_Controle"
    Partenaire   "&Partenaire"
  ;

  * also works;
  * Nom_controle = "&Nom_Controle";
  * Partenaire   = "&Partenaire"; 
run;

    Note: The new data set variables lengths will be set to the length of the values stored in the macro variables.

    A data set is a rectangle of values. It will have a certain number of rows and columns of numeric and / or character types. The SET statement in a DATA step reads one row of the table's column values into the running program data vector -- which are essentially the variables in the DATA step. A DATA step loops automatically and halts automatically on various conditions, such as the last row of a SET table being read.

    I don't know why you have a macro loop %DO I=1 %TO 10. I might speculate you think you need to do this in order to 'update' 10 rows in &new_data_set.

    What is it really doing ? Running the same code 10 times! Without macro the actual code run is akin to the following

    data x; do r = 1 to 10; output; end; run;  %* an original new_data_set;

data x; set x; z=1; run;
data x; set x; z=1; run;
data x; set x; z=1; run;
...

    One additional concern is the code such as

        %if %sysfunc(length(&data_set_detail_mouvement))=29 %then %do; *Creation of a second variable (value conditional to the size of a previous variable);
        %let Partenaire=%sysfunc(substr(&data_set_detail_mouvement, 9, 3)); %end;

    It appears you are grabbing the first 3, 4, or 6th letters of the data set name from a fully qualified libname.dataset where libname is presumed to be sasuser. A safer and more robust version could be

    %let syslast = &data_set_detail_mouvement;
%let libpart  = %scan(&syslast,1,.); 
%let datapart = %scan(&syslast,2,.);
… extract 3, 4, or 6 preface of datapart … 
%* this might be helpful;
%let Partenaire = %scan(&datapart,1,_);