Help using Base SAS procedures

Lining up variables when exporting data to Excel from PROC IML

Reply
Contributor
Posts: 24

Lining up variables when exporting data to Excel from PROC IML

I have a dataset in which three variables (let's call them X, Y, and Z) are to be written into matrices in PROC IML and have some calculations performed upon them. The result of the calculations I need to export to an Excel file. Another column in the original dataset (let's call it N) will not be used in any calculations, but I need it written to the Excel spreadsheet alongside the output of the calculation.

 

How do I ensure that the values in N line up with the corresponding results in the output? I suspect that a loop of some kind is needed (for every record in N, (write X, Y, and Z to matrices, perform the calculations, write N to Excel, write results to Excel)). Am I right about the loop? Is it possible to have several different steps within one loop? And what is the exact syntax for this?

Trusted Advisor
Posts: 1,923

Re: Lining up variables when exporting data to Excel from PROC IML

Posted in reply to dvtarasov

If PROC IML is not changing the order of the data, you would write the IML results into a SAS data set, and then merge with the original data set that contains N.

 

If IML is changing the order of the data, you'd need to include N in your IML matrices and sort/re-arrange N the same as you are doing with the rest of the data.

Contributor
Posts: 24

Re: Lining up variables when exporting data to Excel from PROC IML

Posted in reply to PaigeMiller

Suppose I now have a matrix with two columns (one read from N, the other from X), and I need to multiply the matrix elementwise by the matrix that was read from Y. What is the syntax for leaving the column in the first matrix that came from N unchanged? 

Trusted Advisor
Posts: 1,923

Re: Lining up variables when exporting data to Excel from PROC IML

[ Edited ]
Posted in reply to dvtarasov

Assuming matrix X has variable X in column 1 and variable N in column 2

 

newcolumn=x[:,1]#y;

 

But it sounds like there's really no reason to put X and N into the same matrix in IML.

Ask a Question
Discussion stats
  • 3 replies
  • 442 views
  • 0 likes
  • 2 in conversation