topic Re: How to export dynamic datasets to file in SAS Programming

How to export dynamic datasets to file

Nancy05 — Wed, 03 Aug 2016 23:52:03 GMT

Hi,

Thanks for @Reeza for dynamic sql codes, now I would like to export all datasets to a file.

I have tried the following code and got error. I am not quite sure how to correct " set ELTML.memname;" clause in the following code:

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

%*use Call execute to print each table for specified record;
%*I create the strings as an extra step to allow for debugging;
%*You should remove the PUT statements after you have this working;
data _null_;

SET TABLE_LIST;

str1=cats( "Title 'Mylending Tables-", upcase(memname), "';");
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");


call execute(str1);
call execute(str2);

data Mylending;
	set ELTML.memname;
Where CR_APPL_NO = &CR_APPL_NO;

proc export
 data=Mylending
 dbms=xlsx
 outfile="\\WLGFILE2\<path>\&CR_APPL_NO"
 replace;
RUN;

%mend;

%print_report(60048444);

This is the error message:

ERROR: File ELTML.MEMNAME.DATA does not exist.

I know this is incorrect, but i don't know how to make dataset dynamic and export the data to file.

Help is much appreciated.

Thanks!

Re: How to export dynamic datasets to file

Reeza — Thu, 04 Aug 2016 00:47:57 GMT

You need to use call execute again. You should generate another string that will resolve to the PROC EXPORT code that you need, I would recommend using a dataset WHERE option rather than creating datasets.

Your proc export would look something like the following.

proc export
 data=Mylending. memname (Where cntr_no=&cr_appl_no)
 dbms=xlsx
 outfile="\\WLGFILE2\<path>\&CR_APPL_NO"
 replace;
RUN;

I'm not going to write the code because I believe you should understand the code your implementing/writing and this would show that you understand the concept.

The easiest way is to add a new variable STR3 that resolves to your proc export code above and then pass that to call execute.

The code below should help you develop/test your string.

%let CR_APPL_NO=890234343;

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

%*use Call execute to print each table for specified record;
%*I create the strings as an extra step to allow for debugging;
%*You should remove the PUT statements after you have this working;
data commands;
SET TABLE_LIST;

str1=cats( "Title 'Mylending Tables-", upcase(memname), "';");
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");


*BUILD YOUR PROC EXPORT STRING HERE AS STR3;

*call execute(str1);
*call execute(str2);
*call execute(str3);

run;

Re: How to export dynamic datasets to file

Nancy05 — Thu, 04 Aug 2016 02:35:57 GMT

@Reeza: Thanks for the code and suggestion.

I have added str3 shown below. but I stuck with concatenation with quotation marks. How to concatenation quotation mark " ?

Do you use "&"&" ? I googled but could not find the answer.

Here is my code:

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

%*use Call execute to print each table for specified record;
%*I create the strings as an extra step to allow for debugging;
%*You should remove the PUT statements after you have this working;
/*data _null_;*/
data commands;

SET TABLE_LIST;

str1=cats( "Title 'Mylending Tables-", upcase(memname), "';");
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");
str3=cat("Proc export data=ELTML.", memname, "(Where CR_APPL_NO=&CR_APPL_NO dbms=xlsx outfile outfile=","&"&","\\WLGFILE2\<path>\&CR_APPL_NO","&"&"," replace; RUN;"


call execute(str1);
call execute(str2);
call execute(str3);


%mend;

%print_report(60048444);

Thanks !

Re: How to export dynamic datasets to file

Reeza — Thu, 04 Aug 2016 02:58:18 GMT

I've never been very good at the quotation marks portion myself.

Personally, I would assign a variable to the control number and then use that in my file path so I could use single quotes. You can sandwich single quotes between double quotes. Or you can try using 4 quotes. Or %str() or %nstr

Re: How to export dynamic datasets to file

Kurt_Bremser — Thu, 04 Aug 2016 06:04:14 GMT

Use a combination of single and double quotes to solve the problem

%let value1=yyy;
data _null_;
call execute("data test; set have; where x = '&value1';run;");
run;

will create a data step

data test;
set have;
where x = 'yyy';
run;

while

%let value1=yyy;
data _null_;
call execute('data test; set have; where x = "&value1"; run;');
run;

will create

data test;
set have;
where x = "&value1";
run;

Note that the first version resolves value1 during the data_null_ step, while the second version will resolve value1 when the created data step is compiled.

Re: How to export dynamic datasets to file

RW9 — Thu, 04 Aug 2016 08:41:00 GMT

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.

Re: How to export dynamic datasets to file

Nancy05 — Thu, 04 Aug 2016 22:20:12 GMT

@Kurt_Bremser: Thank you so much for your brilliant suggestion. I have tested the code, this is working.

data test;

infile cards missover;

length first last $20;

input first $ last $ ;

datalines;

jone smith

john wayne

bill

phil hodge

;

run;

data test2;

set test;

name = catx(", ", of last first );

name1 = cat(of last first);

name2 = cats(of last first); /*leading and trailing blanks*/

name3 = catt(of last first); /*trailing blanks*/

run;

data test3;

set test;

where first = 'john';

proc print data = test3;

run;

%let first= john;

data _null_;

call execute("data test3; set test; where first ='&first'; Proc print data = test3; run; ");

run;

%let first=john;

data _null_;

call execute('data test4; set test; where first="&first"; proc print data = test4; run; ');

run;

Apply this principle, I have modified my code. I still got error message. Probably there is something wrong with Proc export function.

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

%*use Call execute to print each table for specified record;
%*I create the strings as an extra step to allow for debugging;
%*You should remove the PUT statements after you have this working;
data _null_;
/*data commands;*/

SET TABLE_LIST;

str1=cats( "Title 'Mylending Tables-", upcase(memname), "';");
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");
str3=cat("Proc export data=ELTML.", memname, " (Where CR_APPL_NO=&CR_APPL_NO) 
dbms=xlsx  outfile='&\\WLGFILE2\<PATH>\&CR_APPL_NO&'
replace; RUN;");


call execute(str1);
call execute(str2);
call execute(str3);


%mend;

%print_report(60048444);

Can anyone spot what is wrong with my str3 code?

Re: How to export dynamic datasets to file

Nancy05 — Thu, 04 Aug 2016 22:24:35 GMT

Sorry, post the code in wrong format. do it again.

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

%*use Call execute to print each table for specified record;
%*I create the strings as an extra step to allow for debugging;
%*You should remove the PUT statements after you have this working;
data _null_;
/*data commands;*/

SET TABLE_LIST;

str1=cats( "Title 'Mylending Tables-", upcase(memname), "';");
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");
str3=cat("Proc export data=ELTML.", memname, " (Where CR_APPL_NO=&CR_APPL_NO) 
dbms=xlsx  outfile='&\\WLGFILE2\<PATH>\&CR_APPL_NO&'
replace; RUN;");


call execute(str1);
call execute(str2);
call execute(str3);


%mend;

%print_report(60048444);

Re: How to export dynamic datasets to file

Nancy05 — Fri, 05 Aug 2016 03:29:59 GMT

@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!

Re: How to export dynamic datasets to file

ChrisNZ — Fri, 05 Aug 2016 05:06:57 GMT

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);

Re: How to export dynamic datasets to file

Reeza — Fri, 05 Aug 2016 05:26:47 GMT

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.

Re: How to export dynamic datasets to file

Reeza — Fri, 05 Aug 2016 05:32:00 GMT

@Nancy05 wrote:

Sorry, post the code in wrong format. do it again.

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

%*use Call execute to print each table for specified record;
%*I create the strings as an extra step to allow for debugging;
%*You should remove the PUT statements after you have this working;
data _null_;
/*data commands;*/

SET TABLE_LIST;

str1=cats( "Title 'Mylending Tables-", upcase(memname), "';");
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");
str3=cat("Proc export data=ELTML.", memname, " (Where CR_APPL_NO=&CR_APPL_NO) 
dbms=xlsx  outfile='&\\WLGFILE2\<PATH>\&CR_APPL_NO&'
replace; RUN;");


call execute(str1);
call execute(str2);
call execute(str3);


%mend;

%print_report(60048444);

What does str3 resolve to in the dataset? You should get code that you can take and paste into a program editor and run. If it doesn't it will at least let you know the issue you need to fix.

Add

put str3;

befoe you Call Execute to see the full value in the log.

OR change the data _null_ to data check, and examine your dataset.

Re: How to export dynamic datasets to file

ChrisNZ — Fri, 05 Aug 2016 06:33:24 GMT

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);

Re: How to export dynamic datasets to file

Kurt_Bremser — Fri, 05 Aug 2016 06:31:31 GMT

The first thing that catches my eye is the nonprint option on the proc sql. This should be noprint.

Re: How to export dynamic datasets to file

Nancy05 — Sun, 07 Aug 2016 22:05:33 GMT

@Reeza: Thanks for the new code. I feel I am so close to the answer but just could not get it working.

The code can produce table_list; But no matter how I modify the code, I still get error message.

                     180
ERROR 180-322: Statement is not valid or it is used out of proper order.

Even I use:

put str3;

I couldn't see how it help me debugging...I also got this warning message:

74         %print_report(60048444);
NOTE: Compressing data set WORK.TABLE_LIST increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.TABLE_LIST created, with 100 rows and 1 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.11 seconds
      user cpu time       0.01 seconds
      system cpu time     0.09 seconds
      memory              5204.03k
      OS Memory           30304.00k
      Timestamp           08/08/2016 09:50:23 AM
      Step Count                        233  Switch Count  24

But I know this piece of code working (Just for helping debugging purpose!) It does not need '&' in front of "\\"

data test;
   infile cards missover;
   length first last $20;
   input first $ last $  ;
datalines;
jone smith
john wayne
bill 
phil hodge
;
run;

%let first=john;
data _null_;
call execute("proc export data=test (where=(first='&first')) 
		outfile='\\WLGFILE2\<PATH>\test2.xlsx' 
		dbms=xlsx 
		replace; 
		RUN;");
run;

This made me wonder whether SAS can handle with dynamic subset?

Re: How to export dynamic datasets to file

Nancy05 — Sun, 07 Aug 2016 22:10:41 GMT

@Kurt_Bremser : Thanks for your comment. I have changed "nonprint" to "noprint" in proc sql. Actually, I don't know the difference, From SAS site, some example use 'noprint", some use 'nonprint'.

Re: How to export dynamic datasets to file

Nancy05 — Sun, 07 Aug 2016 23:11:29 GMT

@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!

Re: How to export dynamic datasets to file

Reeza — Mon, 08 Aug 2016 00:03:47 GMT

@Nancy05

Post what you get from the put statement. It should be valid SAS code that you can copy and run. If it isn't then you have a mistake in your code, which you obviously do. It's most likely a semicolon error. And if you run the pasted copy SAS highlights the line that's the issue. As is, we're trying to guess. That's a waste of everyone's time.

This is also why you should understand what your trying to do. So far I think your still using code that you don't understand which is dangerous and risky. Especially if you end up in a time crunch since you won't be able to modify it if needed. And it's unlikely you'll be able to reuse it either.

Re: How to export dynamic datasets to file

Nancy05 — Mon, 08 Aug 2016 01:54:53 GMT

@Reeza: Thank you so much for your reply.

I confess that I don't fully understand the meaning of put function. How to get the valid SAS code from put statement?

This is what I can find about put function. But it doesn't tell me how to get value from get statement.

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#n0mlfb88dkhbmun1x08qbh5xbs7e.htm

I think that I understand cat function and proc export in static.

Thank you again!

Re: How to export dynamic datasets to file

Reeza — Mon, 08 Aug 2016 02:24:44 GMT

Read your log? You should always read your log...

PUT prints the content of the variable or string to the log.

Functions use parentheses. This is the PUT statement.

http://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n1spe7nmkmi7ywn175002rof97fv.htm

PUT Statement

Writes lines to the SAS log, to the SAS output window, or to an external location that is specified in the most recent FILE statement.