Data step code optimisation to read many data sets ?

Reply
Frequent Contributor
Posts: 137

Data step code optimisation to read many data sets ?

Hey Folks,

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-

data libname2.dataset1;

set libname1.dataset1;

run;

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.

Sincerely,

Charlotte

Esteemed Advisor
Posts: 7,296

Re: Data step code optimisation to read many data sets ?

Have you considered proc datasets?  e.g.:

proc datasets library=source; copy out=dest; run;

see: Base SAS(R) 9.2 Procedures Guide

Grand Advisor
Posts: 17,393

Re: Data step code optimisation to read many data sets ?

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.

Frequent Contributor
Posts: 137

Re: Data step code optimisation to read many data sets ?

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:

Grand Advisor
Posts: 17,393

Re: Data step code optimisation to read many data sets ?

The select statement is optional. Without it, exactly as has typed it copies all datasets in the library.

Is that innovative enough? Smiley Wink

Super Contributor
Posts: 336

Re: Data step code optimisation to read many data sets ?

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 (!);

Proc SQL;

  Connect to ODBC (DSN=sassql);

  Create Table Info_Datamart As

  Select * From Connection to ODBC (ODBC:Smiley FrustratedQLTables (, "DBO", , ));

  Disconnect From ODBC;

Quit;

.. and could it be that you not only want to copy tables but also to filter and group the data.

Grand Advisor
Posts: 9,584

Re: Data step code optimisation to read many data sets ?

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 a1-a100;

select var:  ;

run;

BTW, you can check the dictionary to select the table names you want. Like

proc sql;

select memname into : list separated by ' ' from dictionary.members where libname='SQLlibname' ;

quit;

and put it into proc copy 's select statement.

Xia Keshan

Frequent Contributor
Posts: 137

Re: Data step code optimisation to read many data sets ?

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?*/

proc sql;

select memname into : list separated by ' ' from dictionary.members where libname='SQLlibname' ;/*something more here?*/

quit;

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.

Respected Advisor
Posts: 3,837

Re: Data step code optimisation to read many data sets ?

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.

Capture.PNG

Frequent Contributor
Posts: 137

Re: Data step code optimisation to read many data sets ?

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.

Grand Advisor
Posts: 9,584

Re: Data step code optimisation to read many data sets ?

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*/

proc sql;

select  memname into : list separated by ' '

  from dictionary.members

   where libname='SASHELP' and memtype='DATA' and lowcase(memname) like '%a%';

quit;

/*check the table name we want*/

%put &list;

proc copy in=sashelp out=x memtype=data;

select &list ;

run;


Xia Keshan

Ask a Question
Discussion stats
  • 10 replies
  • 525 views
  • 9 likes
  • 6 in conversation