DATA Step, Macro, Functions and more

How to export dynamic datasets to file

Reply
Contributor
Posts: 61

How to export dynamic datasets to file

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!

Super User
Posts: 19,877

Re: How to export dynamic datasets to file

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;

 

 

 

 

Contributor
Posts: 61

Re: How to export dynamic datasets to file

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

 

Super User
Posts: 19,877

Re: How to export dynamic datasets to file

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

 

 

Super User
Posts: 7,866

Re: How to export dynamic datasets to file

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 61

Re: How to export dynamic datasets to file

Posted in reply to KurtBremser

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

 

Contributor
Posts: 61

Re: How to export dynamic datasets to file

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);
Super User
Posts: 19,877

Re: How to export dynamic datasets to file


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. 

 

 

 

 

Contributor
Posts: 61

Re: How to export dynamic datasets to file

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

 

Super User
Posts: 19,877

Re: How to export dynamic datasets to file

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

Contributor
Posts: 61

Re: How to export dynamic datasets to file

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

Super User
Posts: 19,877

Re: How to export dynamic datasets to file

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.

Super User
Posts: 7,866

Re: How to export dynamic datasets to file

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 61

Re: How to export dynamic datasets to file

Posted in reply to KurtBremser

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

Super User
Posts: 19,877

Re: How to export dynamic datasets to file

[ Edited ]

@Nancy05 

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

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);
Ask a Question
Discussion stats
  • 37 replies
  • 566 views
  • 7 likes
  • 5 in conversation