Hi,
I have written a marco to output multiple tables. I have more than 50 tables. I wonder if there is more elegent way to output the results.
The following SAS code only list three tables -
MLT_CREDIT_APPL,
MLT_PARTY
MLT_PREAPPROVED_CC
libname ELTML BASE "N:\BUILD\INFORMATICA\Mylending";
data Mylending_APPL;
set ELTML.MLT_CREDIT_APPL;
data Mylending_PARTY;
set ELTML.MLT_PARTY;
data Mylending_PREAPPROVED_CC;
set ELTML.MLT_PREAPPROVED_CC;
%LET CR_APPL_NO = 60048444;
* Use a macro variable to subset;
Title "Mylending Tables - MLT_CREDIT_APPL";
PROC PRINT DATA = Mylending_APPL NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_PARTY";
PROC PRINT DATA = Mylending_PARTY NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_PREAPPROVED_CC";
PROC PRINT DATA = Mylending_PREAPPROVED_CC NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
RUN;
Is there a better way to write the code rather then repeat the similar code 50 times?
Thanks!
1. Add SET statement to data _null_ so the table is being used
2. Modify the STR1 to generate the TITLE statement correctly.
This is tested and working.
%macro print_report(CR_APPL_NO);
%*Get all table names from library that start with MLT;
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_;
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;");
put str1;
put str2;
call execute(str1);
call execute(str2);
run;
%mend;
1. Why do you have the first data steps? You can access the datasets directly from the library no need to copy them to work.
2. Which part of the code is going to be repeated, the multiple proc prints with different tables or different where clause values.
The variable is CR_APPL_NO which is primary key for each tables/datasets (I only list three tables here!)
I want to wirte the code so it can:
1. give me all the output where CR_APPL_NO = <CR_APPL_NO>
2. for all the datasets <MLT_XXXXX> in lib ELTML (more than 50 datasets)
I have repeat the codes in data sections 3 times and where clauses 3 times in the last example.
How to make the code more efficient? Also SAS seems could not handle if I output more than 3 datasets.
The objective outcome is that everytime when I run the code, all I need to do is change the input variabel - CR_APPL_NO. So I can get the outputs for all datasets where CR_APPL_NO = <CR_APPL_NO> in lib ELTML .
Can you help please? SAS code are welcome.
Thanks!
How big are these datasets? Are they each unique?
If you could combine them in some way it would help.
If they are unique the simplest way is to create a macro that takes the control # as the parameter. But that does mean writing the full thing out at least once. You could do it dynamically but it looks like you want titles that are unique as well.
Here's a quick mock up. You can put all your proc prints in the macro.
%macro print_age(age);
Proc print data = sashelp.class;
Where age = &age;
Run;
%mend;
%print_age(13);
%print_age(16);
SAS can handle many outputs so I'm not sure what you mean when you say it only handles 3. You do need to clarify that.
PS. You question may be clear to you, but it isn't to us, ergo the questions to clarify 😉
Hi Reeze,
Sorry I didn't make it clear to you.
What I mean it can handle 3 as I have tried 5 and it only come out 3 dataset outputs.
Each dataset is also very big, it contains lots of vaiable. One Dataset could have multiple observations for a given CR_APPL_NO.
libname ELTML BASE "N:\BUILD\INFORMATICA\Mylending";
data Mylending_APPL;
set ELTML.MLT_CREDIT_APPL;
data Mylending_PARTY;
set ELTML.MLT_PARTY;
data Mylending_PREAPPROVED_CC;
set ELTML.MLT_PREAPPROVED_CC;
data Mylending_LOYALTY_SCHEME;
set ELTML.MLT_LOYALTY_SCHEME;
data Mylending_REL_INDIVIDUAL;
set ELTML.MLT_REL_INDIVIDUAL;
%LET CR_APPL_NO = 60048444;
* Use a macro variable to subset;
Title "Mylending Tables - MLT_CREDIT_APPL";
PROC PRINT DATA = Mylending_APPL NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_PARTY";
PROC PRINT DATA = Mylending_PARTY NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_PREAPPROVED_CC";
PROC PRINT DATA = Mylending_PREAPPROVED_CC NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_LOYALTY_SCHEME";
PROC PRINT DATA = Mylending_LOYALTY_SCHEME NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_REL_INDIVIDUAL";
PROC PRINT DATA = Mylending_REL_INDIVIDUAL NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
RUN;
Or I wonder if my option haven't be set up correctly.
Can you see the pattern here? By the way, this is my first marco. Please be patient with me.
Thanks!
Your code above isn't a macro, it's still in base. You are using macro variables (&CR_APPL_NO).
It shouldn't matter how many proc prints you have, so check your log for errors. Something else is causing the issue.
You don't need the data steps, your full macro can be as below:
%macro print_report(CR_APPL_NO);
Title "Mylending Tables - MLT_CREDIT_APPL";
PROC PRINT DATA = ELTML.Mylending_APPL NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_PARTY";
PROC PRINT DATA = ELTML.Mylending_PARTY NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_PREAPPROVED_CC";
PROC PRINT DATA = ELTML.Mylending_PREAPPROVED_CC NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_LOYALTY_SCHEME";
PROC PRINT DATA = ELTML.Mylending_LOYALTY_SCHEME NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
Title "Mylending Tables - MLT_REL_INDIVIDUAL";
PROC PRINT DATA = ELTML.Mylending_REL_INDIVIDUAL NOOBS;
WHERE CR_APPL_NO = &CR_APPL_NO;
RUN;
%mend;
Then you can call the macro as:
%print_report(60048444);
Based on your table name you can make this more dynamic, but I have a feeling this is well beyond your skillset. I'll show it anyways, but you need to take the time to read it and understand it. You call the macro the same as above.
%macro print_report(CR_APPL_NO);
%*Get all table names from library that start with MLT;
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_;
str1=catx(" - ", "Mylending Tables", upcase(memname));
str2=cat("Proc Print Data=ELTML.", memname, "NOOBS;", "Where CR_APPL_NO=&CR_APPL_NO; RUN;");
put str1;
put str2;
call execute(str1);
call execute(str2);
run;
%mend;
@Reeza : Thank you so much for the code! The first macro works well!
Now, I try to get your second macro working... You are right! it is quite beyong my SAS skill. So I try to make it working piece by piece.
Firstly, I try to understand sashelp.vtable. I have tried the code below, but not working... and don't know why.
proc sql;
select distinct memname
from sashelp.vtable;
quit;
run;
or this one:
proc sql;
select *
from sashelp.vtable;
quit;
run;
I have tried the code below, but not working... and don't know why.
I have no idea what "not working" means so I don't know why either. What does not working mean? What are you expecting? Your query should return results in either your listing or html window that shows all table names in all libraries. Similar to proc print.
@Reeza : Sorry, I just realised that our SAS enviorment is down. I will try it later.
Thank you so much for your help!
@Reeza: I can appreciate the beauty of your code, but unfortunately I can't get it working.
I got error message when I run your second marco. Here is the error message:
NOTE: CALL EXECUTE generated line. NOTE: Line generated by the CALL EXECUTE routine. 1 + Mylending Tables - . _________ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 2 + Proc Print Data=ELTML..NOOBS; NOTE: Line generated by the CALL EXECUTE routine. 2 + Where CR_APPL_NO=60048444; _____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.
I am not sure how to fix the code and make it working..
Thanks!
String 1 isn't correct. It should generate a title statement but is missing the title and quotes. Maybe that's enough for you to fix it?
You can just comment out the call execute for STR1 for now and test the rest otherwise. I'll take a look at the code later.
If the main change is only the where clause you might consider using that variable as a BY variable for proc freq or possibly a Page variable for Proc Report or Tabulate. Or possibly a solution using a data step and Call Execute.
Without an idea of the desired result it's a bit hard to come up with more specific suggestions.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.