DDE link to Excel

[svp001]

I have the following problem, the code I am using to link sas with excel through DDE requires the variable, in other words the columns of the table in SAS. The problem is that I am writing a universial program where the column names of the table changes regularly and thus I want oti know is there a way I can "put" all the tables in a column instead of using the "put &variable;" statement.

Here is the code I use:

/* Export category labels to Excel only the top 7*/
%macro excel (variable=,data=,sheet=);
FILENAME ddedata DDE
"excel|[Automated_homeloans.xls]numcases_cat!r2c3:r4c10" notab;
FILENAME ddecmds DDE "excel|system";
data _null_;
set &data;
file ddedata;
put &variable;
run;

%mend excel;

%excel (data=numcases_cat1,
sheet = sheet 1,
variable = name '09'x surname '09'x;
);

 

[klaz2002]

There are a few ways that you can attack this problem. The first way is to fully understand what you want to do. I find it hard to accept that column names (variables) in tables should change. Perhaps that part of your code has not been well planned out. That said, you could make use of the SASHELP.VCOLUMNS dataset that has all the tables and their columns in tabular form. You would still have to build the string of the variable names off of that table.

If your variables are always in the same position, say they come after some header variables, you could use the variable list operator (var1--var10) or if they are not in any position but are numerically named (var1, var2... in any order ) you could use the list operator with one dash ('-').

Let us know some more about how you your sas tables change and we can give you some tips.
Klaz