topic Re: Populating a new column in an existing dataset in SAS/IML Software and Matrix Computations

Populating a new column in an existing dataset

dvtarasov — Fri, 06 Nov 2015 17:12:46 GMT

I need to write the contents of a 1-column matrix into a new column in an existing dataset. Is there a way of doing this in IML such that the values being written into the new column line up with the corresponding content in the old data? (The matrix and the old dataset have the same number of rows).

Re: Populating a new column in an existing dataset

PaigeMiller — Fri, 06 Nov 2015 17:34:42 GMT

You write the contents of the 1-column matrix to a SAS data set (IML commands to use are CREATE and APPEND). Then in a SAS data step, you merge the original data set and the data set created from PROC IML.

Re: Populating a new column in an existing dataset

Rick_SAS — Fri, 06 Nov 2015 17:45:34 GMT

I agree with @PaigeMiller. SAS stores data sets rowwise, so to add a column you actually need to create a new data set. You can append rows to an existing data set, but not columns.

You could do this operation entirely in IML, but it will be more efficient to use the MERGE statement in the DATA step to combine the data (unless all the data are already in IML vectors or matrices).

Re: Populating a new column in an existing dataset

dvtarasov — Fri, 06 Nov 2015 18:39:01 GMT

In other words, with the MERGE statement, the rows in two datasets will line up? (The new dataset is calculated based on the old one, so has the same number of rows, but does not share a common field with it.)

Sorry for these kinds of questions, I'm utterly new to SAS and IML.

Re: Populating a new column in an existing dataset

Rick_SAS — Fri, 06 Nov 2015 18:49:28 GMT

Yes. Here is a simple example that you can study and play with.

data Have;
do i = 1 to 10;
   x = i + 1;
   y = -2*i -4;
   output;
end;
run;

proc iml;
newVar = T(10:1);  /* 10, 9, 8,..., 1 */
create Calc var "newVar";
append;
close Calc;
quit;

data Want;
merge Have Calc;
run;
 
proc print data=Want;
run;

Re: Populating a new column in an existing dataset

PaigeMiller — Fri, 06 Nov 2015 18:50:02 GMT

It is the programmers responsibility to make sure the rows line up. SAS will not guarantee this. If you have done nothing in either IML or outside of IML to change the orders of the rows, then they should line up.

You can guarantee that the rows will line up if you have some sort of row identifier variable, then you can merge the datasets using a BY statement and including this row identifier varialbe in the BY statement. For this to work, you need to have the row identifier in IML as well as in the original data set.

Re: Populating a new column in an existing dataset

dvtarasov — Fri, 06 Nov 2015 19:05:24 GMT

So, to guarantee that MERGE works properly, do I need the new dataset to have two columns: the new content and the common field (to use in the "BY" statement) from the old dataset?

Re: Populating a new column in an existing dataset

PaigeMiller — Fri, 06 Nov 2015 19:14:05 GMT

Either that, or by confirming that your code has not changed the order of the data in IML and has not changed the order of the data in your data step.

Re: Populating a new column in an existing dataset

dvtarasov — Fri, 06 Nov 2015 19:22:38 GMT

Into the Calc dataset, I also want to read the variable "key_num" (or whatever I'll call the common field from the Have dataset) for use in the "BY" statement in the later MERGE. How do I make sure that the values in "newVar" line up with the corresponding values in "key_num"?

Re: Populating a new column in an existing dataset

PaigeMiller — Fri, 06 Nov 2015 19:25:16 GMT

You create your matrix in IML from the two columns in your original data set, key_num and the variable of interest.

Then when you use IML to create a new SAS data set, it will have key_num and the computed column. If there is any need to sort in IML, you'd want to sort both key_num and the data.

Re: Populating a new column in an existing dataset

dvtarasov — Fri, 06 Nov 2015 20:15:38 GMT

How do I perform a computation (for example, elementwise multiplication) on only that column of the matrix which holds the variable of interest? In other words, how do I avoid changing the key value during computations?

Re: Populating a new column in an existing dataset

Rick_SAS — Fri, 06 Nov 2015 20:27:44 GMT

Are you sure that you need IML for this problem? Perhaps the DATA step would be easier for you,

If you want to learn IML, here are some tips for getting started with IML programming. I think after you complete steps 1-3 you should be able to make a good first attempt at this problem.

Re: Populating a new column in an existing dataset

dvtarasov — Fri, 06 Nov 2015 20:33:29 GMT

I, too, am wondering whether IML is how this is done, since I'm not really doing anything that necessitates matrices (all my calculations are elementwise). What I need to do is multiply every value in a column by a certain number; raise every value in a column to a certain power; multiply each value by the corresponding value in another column; and obtain a sum of all the values in a column. Can all of these be done with the Data step?

Re: Populating a new column in an existing dataset

Tom — Fri, 06 Nov 2015 20:56:40 GMT

@dvtarasov wrote:

I, too, am wondering whether IML is how this is done, since I'm not really doing anything that necessitates matrices (all my calculations are elementwise). What I need to do is multiply every value in a column by a certain number; raise every value in a column to a certain power; multiply each value by the corresponding value in another column; and obtain a sum of all the values in a column. Can all of these be done with the Data step?

So let's place the two constants into macro variables to make it easier to modify later.

%let coeffient=1.5;
%let power=3 ;

Now it sounds like you want to do something like this:

data want ;
  set have ;
  new_var = &coefficient*VAR1 * (VAR2 ** &power);
run;

proc means data=want sum;
  var new_var ;
run;

Re: Populating a new column in an existing dataset

dvtarasov — Sat, 07 Nov 2015 01:08:07 GMT

Thanks! I've experimented with this way of doing things and it seems to work for me. I am wondering, however: is it possible, for the OUTPUT statement of PROC MEANS, to produce, not a new dataset, but just a new variable that stores the sum?

Re: Populating a new column in an existing dataset

Tom — Sat, 07 Nov 2015 02:30:27 GMT

@dvtarasov wrote:
Thanks! I've experimented with this way of doing things and it seems to work for me. I am wondering, however: is it possible, for the OUTPUT statement of PROC MEANS, to produce, not a new dataset, but just a new variable that stores the sum?

Not sure what that means. A variable is an attribute or question that has a value recorded for every observation. The sum of a column is not another variable, at least not at the same unit of measure as the original records in the source table.

Now if you want and easy method to generate a sum over a table (or a by group within a table) and then assign that sum as essentially a constant it is easier to do in PROC SQL. In fact it is easier in SAS's PROC SQL than in most vendors SQL implementation.

proc sql;
   create new as
   select id
        , cost
        , sum(cost) as total_cost
        , cost/ calculated total_cost as percent_cost
  from have
  group by id
  ;
quit;

Re: Populating a new column in an existing dataset

dvtarasov — Sat, 07 Nov 2015 16:47:04 GMT

Yes, obtaining the sum over a column (not grouped by anything within the column) and then using that sum as, essentially, a constant in the next step is what I'm trying to do.

Before trying this with my actual data, I experimented on a small "test" dataset which had two columnts, Var1 and Var2. I tried to obtain the sum ("total_cost") of Var1 and then write a new dataset, which would be like the old one but have an additinal column, Var3, that would hold the values of Var2 multiplied by "total_cost". The summing was done as follows:

proc sql;
select sum(var1) as total_cost
from sasdata.olddata;

This part worked; the expected sum was computed. By the next step, however, "total_cost" seems to have gone missing and could not be used in the calculation:

data sasdata.newdata;
set sasdata.olddata;
Var3 = total_cost * Var2;
run;

NEWDATA was indeed created, but the Var3 column is blank "as a result of performing an operation on missing values." For some reason the new dataset also had a "total_cost" column after that, even though I did not ask for such a column. It, too, was blank.

It seems, then, that "total_cost" (what's the term for it, if not "variable?") is no longer available by the time the next step starts. How do I pass it on to the next step?

Re: Populating a new column in an existing dataset

Tom — Sat, 07 Nov 2015 17:00:19 GMT

@dvtarasov wrote:

Yes, obtaining the sum over a column (not grouped by anything within the column) and then using that sum as, essentially, a constant in the next step is what I'm trying to do.

Before trying this with my actual data, I experimented on a small "test" dataset which had two columnts, Var1 and Var2. I tried to obtain the sum ("total_cost") of Var1 and then write a new dataset, which would be like the old one but have an additinal column, Var3, that would hold the values of Var2 multiplied by "total_cost". The summing was done as follows:
proc sql;
select sum(var1) as total_cost
from sasdata.olddata;
This part worked; the expected sum was computed. By the next step, however, "total_cost" seems to have gone missing and could not be used in the calculation:
data sasdata.newdata;
set sasdata.olddata;
Var3 = total_cost * Var2;
run;
NEWDATA was indeed created, but the Var3 column is blank "as a result of performing an operation on missing values." For some reason the new dataset also had a "total_cost" column after that, even though I did not ask for such a column. It, too, was blank.

It seems, then, that "total_cost" (what's the term for it, if not "variable?") is no longer available by the time the next step starts. How do I pass it on to the next step?

So the first PROC SQL step just produced a report. It did not generate anything that could be saved for later use because you did not ask it to do that. It did not report any of the other variables in the old data either since you did not include them in the select statement. You can make a new dataset with the total as new column and copy forward all of the data by using a statement like this.

proc sql noprint;
create table newdata as 
   select *,sum(var1) as total_cost
   from olddata
;
quit;

The reason your data step made an empty variable is because the only reference to the variable was on the right hand side fo the assignment statement. So SAS saw that you were referencing an previously undefined variable and created one for you.

Here is a method that you can use to calculate and use a total in a single data step. But doing this means that you have to take control of when SAS reads and writes observations.

data newdata ;
   if _n_=1 then do while  (not eof);
      set olddata end=eof;
      total_cost = sum(total_cost,var1);
   end;
   retain total_cost;
   set olddata ;
   var3 = total_cost * var2;
run;

Basically the IF statement/ DO loop will make it read in the whole dataset when the data step first starts and sum var1 into the new varaible TOTAL_COST. The rest of the data step will process normally.