BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kjohnsonm
Lapis Lazuli | Level 10

Hello all,

Does anyone have a hint on how I can read just the table names in an entire MS SQL libname into sas?

 

For example:

 

proc sql;
create table temp as
SELECT TABLE_NAME
FROM my_lib.[INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME LIKE 'xw_%';
QUIT;

 

I have tried dropping the [], I have tried dropping ".[COLUMNS]"

I have tried making a temp table from the source MS SQL DB on this data, so I can just read a non-schema table directly like any other regular data set.   It shouldn't be that hard what am I missing?

last I have tried a proc

print data=sashelp.vcolumn noobs;

var memname name type [... etc];

where libname='my_lib';

run;

 

This last item seems to have hung my SAS app.  I can read and write to this libname for other tables.   TIA.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Also for sashelp.vcolumns the entries are case sensitive. 

Youve stated table names, is there a reason your not using vtable instead of vcolumn?

View solution in original post

5 REPLIES 5
Reeza
Super User

Try a proc datasets though sometimes for DB it's all blanks. 

kjohnsonm
Lapis Lazuli | Level 10
"Try a proc datasets though sometimes for DB it's all blanks." ---I am not sure what you mean buy this idea, however your other comment was spot on.
Reeza
Super User

See the output from the following. 

 

Proc datasets library=libname;

run;quit;

 

 

Reeza
Super User

Also for sashelp.vcolumns the entries are case sensitive. 

Youve stated table names, is there a reason your not using vtable instead of vcolumn?

kjohnsonm
Lapis Lazuli | Level 10
Thanks for the reminder. I forgot about case ...

proc print data=sashelp.vcolumn noobs;
where lower(libname)='my_lib';
run;

PS I also figured out the MS SQL side, the "table name" is MS's word sorry for that one, I did not fully translate the code from MS SQL to SAS.

SELECT distinct TABLE_NAME INTO my_xw_tables FROM [my_db].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME like 'xw_%';

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
  • 5 replies
  • 1578 views
  • 2 likes
  • 2 in conversation