Search code examples
sasdde

SAS: use oberservation

I have a question on how to use the value from a SAS database in another command. In my case, I have a database with two variables (cell and res). "Cell" contains a reference to a cell in an Excel sheet where the value of "res" should be copied.

So I would like to use the value stored in "cell" in my command linking to the Excel sheet. This code does not work (concatenating with || does not work.)

DATA _null_; 
SET test;
FILENAME ExcelTmp DDE "EXCEL|[&myInputTemplate.]&mySheet.!" || cell;
FILE ExcelTmp NOTAB LRECL=7000;
PUT res;
RUN;

Error message: 

ERROR 23-2: Invalid option name ||.

1491! DDE "EXCEL|[&myInputTemplate.]&mySheet.!" || cell;
ERROR: Error in the FILENAME statement.
ERROR 23-2: Invalid option name cell.

1492      FILE ExcelTmp NOTAB LRECL=7000;
ERROR 23-2: Invalid option name NOTAB.

If I write 

FILENAME ExcelTmp DDE "EXCEL|[&myInputTemplate.]&mySheet.!R1C1:R1C1";

then the value is written to cell A1 in Excel.

Is there some similar approach that works without invoking a macro?

Thanks for your help!

Christoph

Solution

  • The usual way to use values from a dataset as a part of command/statement is CALL EXECUTE routine:

    DATA _null_; 
    SET test;
    call execute("DATA _NULL_;");
    call execute(cats("FILENAME ExcelTmp DDE ""EXCEL|[&myInputTemplate.]&mySheet.!",cell,""";"));
    call execute("FILE ExcelTmp NOTAB LRECL=7000;");
    call execute("PUT '"||res||"';");
    call execute("RUN;");
run;

    This code generates DATA-steps that stacked up in a buffer and will be executed after the step above is executed. So basically you will generate as many DATA NULL steps as you have records in your test dataset.