DATA Step, Macro, Functions and more

Elegent way to output multiple tables

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

Elegent way to output multiple tables

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!


Accepted Solutions
Solution
‎08-02-2016 09:54 PM
Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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;

View solution in original post


All Replies
Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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. 

Contributor
Posts: 61

Re: Elegent way to output multiple tables

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!

 

 

Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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);
Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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 😉

Contributor
Posts: 61

Re: Elegent way to output multiple tables

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.Smiley Happy

 

Thanks!

Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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;

 

Contributor
Posts: 61

Re: Elegent way to output multiple tables

@Reeza : Thank you so much for the code! The first macro works well! Smiley Happy

 

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;
Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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.

Contributor
Posts: 61

Re: Elegent way to output multiple tables

@Reeza : Sorry, I just realised that our SAS enviorment is down. I will try it later.

Thank you so much for your help!

Contributor
Posts: 61

Re: Elegent way to output multiple tables

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

Super User
Posts: 17,837

Re: Elegent way to output multiple tables

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. 

Super User
Posts: 10,500

Re: Elegent way to output multiple tables

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.

Contributor
Posts: 61

Re: Elegent way to output multiple tables

Sorry, I don't understand your meaning of post. I think I have clearly stated what I want.


Super User
Posts: 5,257

Re: Elegent way to output multiple tables

What I think @ballardw is going for is to avoid creating all those data sets: a single reporting PROC can separate each CR_APPL_NO in a single pass.
Data never sleeps
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 25 replies
  • 496 views
  • 3 likes
  • 5 in conversation