How to export dynamic datasets to file

[RW9]

Sorry, I am looking at the code and it is not clear to me.  Why are you printing the data to the output window, this doesn't seem to have any benefit that I can see.  Secondly why are you using proc export, that gives a very basic data dump, with no nice formatting or anything else.  What is the Excel file for, if its for output then ods tagsets.excelxp would be better suited, if its for data transfer then really, don't use Excel - use a proper data transfer format.  Thirdly, why is this all in macro in the first place, I see nothing here that requires it.  Also there is no need to create a dataset just to work on, here is an example:

%macro Print (var=);
  data _null_;
    set sashelp.vtable (where=(libname="SASHELP" and memname="CLASS"));
    by memname;
    call execute(cats('title "My Lending - ',memname,'";
                       proc export data=sashelp.class (where=(sex="&VAR."))
                                    outfile="s:\temp\rob\',memname,'.xlsx" dbms=xlsx replace;
                       run;'));
  run;
%mend Print;

%Print (var=M);

As a final note, please look at code formatting, it is far more important than what your code does that other people can easily read and maintain it.

[Nancy05]

@RW9 : Thank you so much for your suggestion and code. I took a while to understand your code. I have tried out your code and working well with one dataset. (eg. SASHLP.CLASS)

I try to apply your code to my situation. This is best I could think of. The problem is that I am not quite sure how to determin memname as menname like 'MTL%'...

The following code still got error, but I am sure how to determin memname...as my memname is not a dataset, but many (more than 90 dataset actually) and all started with 'MLT".

%macro Print (var=);
  data _null_;
   set sashelp.vtable (where=(libname="ELTML" and  upper(memname) like 'MLT%'));
   by memname;
   call execute(cats('title "My Lending - ',memname,'";
                       proc export data=sashelp.vtable (where=(libname="ELTML" and  upper(memname) like 'MLT%'))
					    outfile="\\WLGFILE2\<Path>\',memname,'.xlsx" 
						dbms=xlsx 
						replace;
                       run;'));
  run;
%mend Print;

%Print (var=60048444);

Any more suggestions would be appreciated.

Thanks!

[ChrisNZ]

You really need to pay attention to your quotes Nancy.

 Edit: I fixed the syntax, but the logic is odd.  No time to look now though. Later if no one replied.

%macro Print (var=);
  data _null_;
   set sashelp.vtable (where=(libname="ELTML" and  upper(memname) like 'MLT%'));
   by memname;
   call execute(cats('title "My Lending - ',memname,'";
    proc export data=sashelp.vtable (where=(libname="ELTML" and  upper(memname) like "MLT%"))
    outfile="\\WLGFILE2\<Path>\',memname,'.xlsx" 
    dbms=xlsx 
    replace;
    run;'));
  run;
%mend Print;

%Print (var=60048444);

[Reeza]

Note that your code doesn't use the macro variable VAR anywhere, so your datasets that are exported are the full datasets, not the subset on the variable. 

I'm not sure why you're not including in the original macro above and trying a new one either.  Its the same methodology as above, just subset for only the export. 

[ChrisNZ]

I think you mean:

%macro Print (var=);
  data _null_;
   set SASHELP.VTABLE (where=(LIBNAME="ELTML" and upper(MEMNAME) like 'MLT%'));
   call execute(cats('title "My Lending - ',MEMNAME,'";
            proc export data=ELTML.',MEMNAME,"(where CR_APPL_NO=&var)",'
            outfile="\\WLGFILE2\<Path>\',MEMNAME,'.xlsx" 
            dbms=xlsx 
            replace;
            run;'));
  run;
%mend Print;

%Print (var=60048444);

[Nancy05]

@ChrisNZ: Thank you so much for your code! The good news is the code is working without any syntax error

But the problem is: It produce nearly 100 spreadsheets ( 1 sheet per databset/table), but its contents exactlly the same.

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: The export data set has 100 observations and 41 variables.
NOTE: "\\WLGFILE2\BNZT Groups\<PATH>\MLT_SECURED_PROD.xlsx" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           2.46 seconds
      user cpu time       0.49 seconds
      system cpu time     1.27 seconds
      memory              7950.21k
      OS Memory           183580.00k
      Timestamp           08/08/2016 10:59:50 AM
      Step Count                        626  Switch Count  28
      

It doesn't help even I change one line code as below:

	outfile="\\WLGFILE2\<PATH>\', &var.,'.xlsx" 

Do you have any further suggestions?

Thanks!

[ChrisNZ]

The contents is selected by the where clause. What's your code?

[Nancy05]

@ChrisNZ : The following code is working without any syntax error. It produce 404 lines notes.

%macro Print (var=);
  data _null_;
   set sashelp.vtable (where=(libname="ELTML" and upper(memname) like 'MLT%'));
   by memname;
   call execute(cats('title "MyLending - ',memname,'";
                       proc export data=sashelp.vtable (where=(libname="ELTML" and upper(memname) like "MLT%"))
					    outfile="\\WLGFILE2\<PATH>\', memname,'.xlsx" 
						dbms=xlsx 
						replace;
                       run;'));
  run;
%mend Print;

%Print (var=60048444);

All spreadsheets are produced at the designated location with <memname>  as file name. But its contents does not held correct informaiton, it just repeated itself with identical contents.

Thanks for taking time to looking at the code!

[ChrisNZ]

You don't use var. What is 60048444 ?

[Nancy05]

@ChrisNZ : 60048444 is a value of primary key variable - CR_APPL_NO. May be should be written as :

%print (CR_APPL_NO=60048444);

var is CR_APPL_NO.

What I want is expressed in the following code (meaning only; with sytax error)

%macro Print (CR_APPL_NO=);
  data _null_;
   set sashelp.vtable (where=(libname="ELTML" and upper(memname) like 'MLT%' and CR_APPL_NO =&CR_APPL_NO));
   by memname;
   call execute(cats('title "MyLending - ',memname,'";
                       proc export data=sashelp.vtable (where=(libname="ELTML" and upper(memname) like "MLT%"))
					    outfile="\\WLGFILE2\<PATH>\', %CR_APPL_NO,'.xlsx" 
						dbms=xlsx 
						replace;
                       run;'));
  run;
%mend Print;

%Print (CR_APPL_NO=60048444);

Put into plain language:

I want print out all the contenst in datasets with libname "ELTML" and memname starting with "MLT" where primary key called "CR_APPL_NO" - 600484444 into a file named as CR_APPL_NO variable.  (of course, primary key value can be changed, it could be any value with 9 digits. )

Hope this make some sense to you.

Thanks!

[ChrisNZ]

@Nancy05 That's what I thought and that's what the code I provided should do.

[Nancy05]

@ChrisNZ : unfortuntatly, It doesn't. There is no syntax error but logic error.

[ChrisNZ]

The code you should use is message #19.

You are not using it afaics. Why?

If it fails please post full code and log.

[Nancy05]

@ChrisNZ: I run the code in message 20 (I think you mean message #20).

Here is code:

%macro Print (var=);
  data _null_;
   set SASHELP.VTABLE (where=(LIBNAME="ELTML" and upper(MEMNAME) like 'MLT%'));
   call execute(cats('title "My Lending - ',MEMNAME,'";
            proc export data=ELTML.',MEMNAME,"(where CR_APPL_NO=&var)",'
            outfile="\\WLGFILE2\<PATH>\',MEMNAME,'.xlsx" 
            dbms=xlsx 
            replace;
            run;'));
  run;
%mend Print;

%Print (var=60048444);

Here is log:

                                                          The SAS System                              08:40 Tuesday, August 9, 2016

1          %_eg_hidenotesandsource;
5          %_eg_hidenotesandsource;
39         
40         %macro Print (var=);
41           data _null_;
42            set SASHELP.VTABLE (where=(LIBNAME="ELTML" and upper(MEMNAME) like 'MLT%'));
43            call execute(cats('title "My Lending - ',MEMNAME,'";
44                     proc export data=ELTML.',MEMNAME,"(where CR_APPL_NO=&var)",'
45                     outfile="\\WLGFILE2\<PATH>\',MEMNAME,'.xlsx"
46                     dbms=xlsx
47                     replace;
48                     run;'));
49           run;
50         %mend Print;
51         
52         %Print (var=60048444);

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: The map data sets in library MAPSGFK are based on the digital maps from GfK GeoMarketing and are covered by their Copyright. 
      For additional information, see http://support.sas.com/mapsonline/gfklicense.
NOTE: There were 0 observations read from the data set SASHELP.VTABLE.
      WHERE (LIBNAME='ELTML') and UPCASE(MEMNAME) like 'MLT%';
NOTE: DATA statement used (Total process time):
      real time           3.22 seconds
      user cpu time       0.49 seconds
      system cpu time     1.29 seconds
      memory              7510.75k
      OS Memory           29980.00k
      Timestamp           09/08/2016 08:47:17 AM
      Step Count                        2  Switch Count  26
      

53         
54         %_eg_hidenotesandsource;
67         
68         
69         %_eg_hidenotesandsource;
72         

I need gfklicense to run this query?

[Reeza]

No, but that doesn't make sense, because none of the other code would have worked either.

What happens when you run each of the codes below:

data table_list1;

set sashelp.vtable;

where libname='ELTML' and upper(memname) like 'MLT%';

run;

	proc sql;
		create table table_list2 as select distinct memname from sashelp.vtable where 
			libname='ELTML' and upper(memname) like 'MLT%';
	quit;