How to export dynamic datasets to file

[Nancy05]

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!

[Reeza]

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;

[Nancy05]

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

[Reeza]

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

[Kurt_Bremser]

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

eg

%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.

[Nancy05]

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

[Nancy05]

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

[Reeza]

---

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

[Nancy05]

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

[Reeza]

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

[Nancy05]

@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#n0mlfb88dkh...

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

Thank you again!

[Reeza]

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#n1spe7nmkmi7ywn...

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.

[Kurt_Bremser]

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

[Nancy05]

@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'.

[Reeza]

@Nancy05 

The code below should work as is, not sure why formatting got messed up though 😞

It generates one file per dataset. So for each ID you get 50 XLSX files. 

You may want to consider one file with 50 sheets instead?

%macro print_report(CR_APPL_NO);
	proc sql noprint;
		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..xlsx' replace; RUN;");
		call execute(str1);
		call execute(str2);
		call execute(str3);
	run;

%mend;

%print_report(60048444);