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

I have an output table that contains 300+ variables from 30 different tables that are joined by UNION, which is used for modelling. I have created a macro that creates a report with a number of statistics, such as mean, min/max values etc. using this output table. I am trying to add a column to the report that details which table(s) the variables come from. I say table(s) as some of the variables are shared across different tables. I want to avoid having the same variable in the report multiple times as the statistics are the same irrespective of what table the variable comes from. Is there an efficient way to do this?

 

SAS Enterprise Guide 7.1

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Well, you can retrieve the information but you will have to go back to the original 30 tables.  Once you have put them together, there's no way to know how you obtained the fields. 

 

It's easier if the original 30 tables are all stored in the same folder:

 

proc sql;

create table sources as select name, memname from dictionary.columns

where libname='my_libname' and memname in ('SOURCE1', 'SOURCE2', ... 'SOURCE30');

run;

 

Then you can easily process the result.  For example:

 

proc sort data=sources;

by name;

run;

 

proc print data=sources;

by name;

id name;

var memname;

run;

 

If your original 30 tables are actually stored in more than 1 folder, the WHERE clause becomes more complex, and you might want to select LIBNAME in addition to MEMNAME and NAME.

View solution in original post

2 REPLIES 2
Astounding
PROC Star

Well, you can retrieve the information but you will have to go back to the original 30 tables.  Once you have put them together, there's no way to know how you obtained the fields. 

 

It's easier if the original 30 tables are all stored in the same folder:

 

proc sql;

create table sources as select name, memname from dictionary.columns

where libname='my_libname' and memname in ('SOURCE1', 'SOURCE2', ... 'SOURCE30');

run;

 

Then you can easily process the result.  For example:

 

proc sort data=sources;

by name;

run;

 

proc print data=sources;

by name;

id name;

var memname;

run;

 

If your original 30 tables are actually stored in more than 1 folder, the WHERE clause becomes more complex, and you might want to select LIBNAME in addition to MEMNAME and NAME.

jamesatkinson
Calcite | Level 5

Great - thanks. Exactly what I needed.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 2 replies
  • 3097 views
  • 0 likes
  • 2 in conversation