BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nancy05
Quartz | Level 8

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 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

25 REPLIES 25
Reeza
Super User

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. 

Nancy05
Quartz | Level 8

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!

 

 

Reeza
Super User

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);
Reeza
Super User

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 😉

Nancy05
Quartz | Level 8

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!

Reeza
Super User

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;

 

Nancy05
Quartz | Level 8

@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;
Reeza
Super User
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.

Nancy05
Quartz | Level 8

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

Thank you so much for your help!

Nancy05
Quartz | Level 8

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

Reeza
Super User

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. 

ballardw
Super User

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.

Nancy05
Quartz | Level 8
Sorry, I don't understand your meaning of post. I think I have clearly stated what I want.


LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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