CODEWITHSUNDEEP
sas
Maximilian
Maximilian

Reputation: 4229

Append new column to existing table using SAS

I have a do loop in which I do calculation on new variable and results are stored as additional column, this column-s (at each iteration) should be attached to the output table defined by macro.

Here on SO something similar has been asked but the answer is not acceptable, the last answer is not compatible with sas command but very close, getting incomplete script with following:

    proc sql;
         update &outlib..&out. 
         set var._iqr = b.&var._iqr
         from &outlib..&out. as a 
         left join cal_resul as b
         on a.id_client=b.id_client 
         and a.reference_date=b.reference_date; 
    quit;

Here is my attempt which works but very slow:

    proc sql; create table &outlib..&out. as select * from &inlib..&in.; quit; /* the input is as a basis for output table */
    proc sql; alter table &outlib..&out. add &var._iqr numeric; quit; /* create empty column to be filled at each iteration */

    proc sql;
         update &outlib..&out. as a
         set &var._iqr=(select b.&var._iqr from cal_resul as b
         where a.id_client=b.id_client 
         and a.reference_date=b.reference_date 
         and a.data_source=b.data_source);
    quit;

Attempt 2:

This is somewhat faster:

    proc sort data=cal_resul; by id_client reference_date data_source; run;

    data &outlib..&out.;
         update &outlib..&out. cal_resul; 
         by id_client reference_date data_source;
    run;

Simple left join (adding new column into existing table is way faster) but with left join I did not figure out how I can update (always retain the same dataset) the &outlib..&out. at each iteration. Many thanks for any help;

Upvotes: 0

Views: 3657

Answers (1)

Tom
Tom

Reputation: 51566

If you want to ADD a variable to a dataset you will have to make a new dataset. (Your ALTER TABLE statement will create a new dataset and copy over all of the observations.)

Looks like your data has three key variables. So use those in merging the new data to the old.

For example to make a new variable in HAVE named EXAMPLE_IQR using the variable EXAMPLE in the dataset NEW you could use code like this. I have used macro variables to show how you might use those macro variables as the parameters to a macro. It sounds like you don't want the process to add new observations to the existing dataset so I have added a check for that using the IN= dataset option.

%let base=work.have;
%let indata=work.new;
%let var=example;

data &base ;
   merge &base(in=inbase)
         &indata(keep=id_client reference_date data_source &var 
                 rename=(&var=&var._iqr)
                )
   ;
   by id_client reference_date data_source;
   if inbase;
run;

Upvotes: 1

Related Questions