proc export problem

[Medvedeff]

I am having an issue trying to write out a bunch of different datasets to excel spreadsheets. The problem lies in the fact that I need to maintain multiple datasets for multiple product types. Something like...

product_1_variable_1.xls
product_1_variable_2.xls
product_1_variable_3.xls
product_1_variable_1.xls
product_2_variable_2.xls
product_3_variable_3.xls
product_1_variable_1.xls
product_2_variable_2.xls
product_3_variable_3.xls

...except there are so many products and variables that is too much to simply change manually.

I have tried putting a macro into the quoted string following the outfile statement in the Proc Export function, but SAS wont recognize it.

I see two ways to resolve this, but I dont know how to do either...

1. Somehow include a macro in the quoted string, so that I can hardcode the variable into the statement but put the product into a macro (%let product=type1 and then call it with &product).
2. Batch process the entire work folder through a proc export, wherein the output file would maintain the name that it has in the work folder.

Does anybody have any suggestions here?

 

[klaz2002]

This is how I understand your problem. You have a dataset or several datasets that have data. You want to export the data to Excel but only a subset of your data. This subset will be stored in a single Excel file with the name of the file as an identifyer. (ie. Product_1_Variable_1.xls).

You want to name the Excel file using the product code (ID) and the name of the variable. You have many such subsets and do not want to manually change the file name.

Solution:

You need to do this using SAS Macro. First generate your dataset. Then use the CALL SYMPUT function to generate a list of variable names. Then loop through the list to generate your Excel files.

I could show you an example, but lets make sure that I have your scenerio right.

Klaz

 

[Medvedeff]

That is mostly correct. However, the different products are in different datasets, each of which will be run through the program individually.

As it stands now, I have a &target statement at the start of the program so I only need to change the name of the dataset once. I was hoping that the program would be developed so that changing the name of the input dataset in only that one place would then filter out into the rest of the program.

Does that make sense?

 

[klaz2002]

But what about your different products?

 

[klaz2002]

Here is what I did with a similar issue. I had many datasets that had similar variables that I wanted to count. So I wrote myself a macro to cycle through the datasets and executed my code. It helped that I had the names of the datasets in question in a Dataset.

%macro count;
  data _null_;
    set datasetnames end=last;
    call symput('dsname'||trim(left(put(_n_,8.))),upcase(MyDSName));
    if last then
      call symput('limit',trim(left(put(_n_,8.))));
   run;

   %do i=1 %to &limit;
      ods pdf file="c:\temp\&&dsname&i..PDF";
        proc print data = &&dsname&i;
        run;
      ods pdf close;
   %end;
%mend count;
%count;

I hope this helps you.
Klaz