11-25-2014 11:28 AM
I have a library created using SAS ACCESS to Microsoft SQL Server. Having established the connection, my objective is read and write those in the SAS environment in a different library.
Well, I believe I can read with a simple set statement when reading a database table, for example-
Is the above right?or please provide me a better solution
However, I need to pull over 50 tables, or in other words if I want to pull 'n' number of data sets that I want from a library that may have many tables. Can anybody provide me with an optimized reusable macro/solution that this can be achieved in one go(run) without having to run one by one time and again.
I'd appreciate any help.
11-25-2014 12:22 PM
or in SAS 9.4 Proc Copy.
One thing to be wary of is table/column name lengths in your migration.
I know with libname the SQL table with names over 32 characters are not displayed. Not sure how proc datasets will treat those datasets.
Variables with names over 32 characters are truncated to 32 characters.
11-25-2014 01:03 PM
Thank you, makes sense and looks pretty straight forward with a select statement in proc datasets mentioning the dataset list if i understood correctly. But, if the list is too long or if the list keeps changing, is there any alternative way?. I'm wondering ways to auto generate the wanted list of datasets from the total number present in the library. An innovative algorithm perhaps? :smileyplus:
11-25-2014 01:10 PM
11-26-2014 03:06 AM
Just an idea. You might want to check the contents of the SQL-library first, then filter the relevant views and tables, then maybe rename them for SAS (if the names as Reeza said are to long), then select the files you would like to copy into a macro variable (only one way among many others), and finally run a loop to transfer the data from SQL to a SAS-library.
* Show contents of your datamart;
Libname sqlserv ODBC Schema=DBO DSN=sassql; * check schema and data source name (!);
Connect to ODBC (DSN=sassql);
Create Table Info_Datamart As
Select * From Connection to ODBC (ODBC:QLTables (, "DBO", , ));
Disconnect From ODBC;
.. and could it be that you not only want to copy tables but also to filter and group the data.
11-26-2014 07:59 AM
As Arthur.T pointed out. Check proc copy .
It support many ways to list dataset name like sas variable name. Like :
proc copy in=in out=out memtype=data;
select var: ;
BTW, you can check the dictionary to select the table names you want. Like
select memname into : list separated by ' ' from dictionary.members where libname='SQLlibname' ;
and put it into proc copy 's select statement.
11-26-2014 10:58 AM
Thanks Art and Reeza, Cool thinking Xia, That's the one step I was looking for before the Proc that does the job. I can see in mind's eye that your idea would complete a neat process. However, just one last thing, can you please elaborate a bit more in your proc sql step-
/*where and how that exactly filters the list we want?*/
select memname into : list separated by ' ' from dictionary.members where libname='SQLlibname' ;/*something more here?*/
coz the above looks like that's gonna take all the data sets from the specified libref. Am i making sense?Or am i being silly in missing out to add something simple in the where clause? Please accept my apologies if i am wrong.
11-26-2014 01:07 PM
You probably would want:
...where libname='<LIBREF>' and memtype='DATA' and upcase(memname) like '<NAME%PATTERN>'
I don't have access to a data base right now but in dictionary.members there is also a column "dbms_memtype". Not sure what the value would be for SQL tables but eventually you would want to use this value instead of "memtype='DATA'" to only select tables.
11-26-2014 03:11 PM
yes Sir, your idea is something very close to what i want, almost there. Just not quite fully there yet. i'm just about to look into dbms_memtype.
11-27-2014 07:49 AM
Patrickhas already given you a good explanation . Here is an example . Assuming SASHELP is your SQL library.
I copy the table name which contains 'a' into c:\temp\
libname x v9 'c:\temp';
/*select the table name we need*/
select memname into : list separated by ' '
where libname='SASHELP' and memtype='DATA' and lowcase(memname) like '%a%';
/*check the table name we want*/
proc copy in=sashelp out=x memtype=data;
select &list ;