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!
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;
@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 !
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
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.
@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?
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);
@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.
@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?
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.
@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.
I think that I understand cat function and proc export in static.
Thank you again!
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.
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.
The first thing that catches my eye is the nonprint option on the proc sql. This should be noprint.
@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'.
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);
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.