BookmarkSubscribeRSS Feed
qwererty
Calcite | Level 5


Hi,

Iam hoping for some help, within DI studio I have various External file objects set up, I would like to be able to query all these objects and pull back the column headers.  I cannot seem to find any documentation about how to access the External file objects, does anyone have any idea on how this can be done?

Thanks

Tom

1 REPLY 1
BrunoMueller
SAS Super FREQ

In SAS Data Integration Studio External Files are read using the File Reader transformation, this will read out the necessary information and build a DATA Step from it. So usually there is no need to build your own transformation.

If you want to read out this information in your code, then there is the SAS(R) 9.4 Language Interfaces to Metadata.

The Metadata Model is described in the documentation, Information on an External file is here SAS(R) 9.4 Metadata Model: Reference

To query the Metadata Server for information about Metadata Objects, one can use Proc Metadata, or you can use DATA Step functions.

Find below a sample program, that will read out all External File definitions and some information about each column within a file. In order to be able to communicate with a Metadata Server you have to set the appropriate Metadata Server related SAS System Options such as  METASERVER, METAUSER, METAPASS, ... If you run the program using SAS Enterprise Guide, you might already have a connection to the Metadata Server

data _null_;
 
length
    file_uri $
256
    file_name $
256
    file_puri $
256
    file_pname $
256
    col_uri $
256
    col_name $
60
    col_sasname $
32
    col_sastype $
1
  ;
  call missing(OF file_:, OF col_:);

  n =
1;
  nobj=metadata_getnobj(
"omsobj:ExternalTable?@Id contains '.'", n, file_uri);

 
do i = 1 to nObj;
    rc = metadata_getnobj("omsobj:ExternalTable?@Id contains '.'", i, file_uri);
    rc = metadata_getattr(file_uri, "Name", file_name);
    rc = metadata_getnasn(file_uri,
"OwningFile", n, file_puri);
    rc = metadata_getattr(file_puri,
"Filename", file_pname);
    putlog "INFO: Table " i= rc= file_uri= file_name= file_puri= file_pname=;
    nCols = metadata_getnasn(file_uri, "Columns", n, col_uri);
   
do j = 1 to nCols;
      rc = metadata_getnasn(file_uri, "Columns", j, col_uri);
      rc = metadata_getattr(col_uri,
"Name", col_name);
      rc = metadata_getattr(col_uri, "SASColumnName", col_sasname);
      rc = metadata_getattr(col_uri, "SASColumnType", col_sastype);
      putlog "INFO: Column " j= rc= col_uri= col_name= col_sasname= col_sastype=;
    end;
 
end;
run;

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 829 views
  • 0 likes
  • 2 in conversation