Read all dataset in a library and store its proc content output in multiple csv files

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Read all dataset in a library and store its proc content output in multiple csv files

Hi All,

 

I need to create a csv file for each of the dataset present under a library, having the data of proc content output of each dataset.

A macro will solve this, i am able to get the list the dataset with:

 

proc sql;
create table columns as
select distinct(memname) as table_name
from dictionary.columns
where libname = 'WRK'
;
quit;

 

but how to proceed to read all the file names and fetch them to generate a csv having their proc contents is what i am stuck with...

 

Thanks in advance!!


Accepted Solutions
Solution
‎06-01-2016 03:51 AM
Esteemed Advisor
Posts: 6,650

Re: Read all dataset in a library and store its proc content output in multiple csv files

When you read from the sashelp tables, you can specify the filevar= option in the file statement and then dynamically assign the output filename from the table name in the input dataset. This way you can do everything in a single data step.

 

ie

data _null_;
set sashelp.vcolumn (where=(libname="YOURLIB"));
by memname;
if first.memname then outfilename = '$HOME/desc_' !! trim(memname) !! '.csv';
file "dummy.dat" filevar=outfilename dlm=',';
put name type length;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,650

Re: Read all dataset in a library and store its proc content output in multiple csv files

You're probably better off reading the metadata from sashelp.vtable and sashelp.vcolumn.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

Re: Read all dataset in a library and store its proc content output in multiple csv files

[ Edited ]

Reading the metadata is not the issue, but reading it and putting it into a csv file as a loop for all the dataset within a library is the issue. A separate csv file needs to be created for the metadata of each dataset

Solution
‎06-01-2016 03:51 AM
Esteemed Advisor
Posts: 6,650

Re: Read all dataset in a library and store its proc content output in multiple csv files

When you read from the sashelp tables, you can specify the filevar= option in the file statement and then dynamically assign the output filename from the table name in the input dataset. This way you can do everything in a single data step.

 

ie

data _null_;
set sashelp.vcolumn (where=(libname="YOURLIB"));
by memname;
if first.memname then outfilename = '$HOME/desc_' !! trim(memname) !! '.csv';
file "dummy.dat" filevar=outfilename dlm=',';
put name type length;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 10

Re: Read all dataset in a library and store its proc content output in multiple csv files

Hi Kurt

Thanks for the resolution, but file creation is giving me an error:
1140 file "dummy.dat" filevar=outfilename dlm=',' ;
ERROR: A Physical file reference (i.e. "PHYSICAL FILE REFERENCE" ) or an aggregate file
storage reference (i.e. AGGREGATE(MEMBER) ) reference cannot be used with the FILEVAR=
option.


seems some limitation for the filevar value, is there a way to overcome this.
Esteemed Advisor
Posts: 6,650

Re: Read all dataset in a library and store its proc content output in multiple csv files

Ups, missed to correct that before posting.

Should be

file dummy filevar=outfilename dlm=',';
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 441 views
  • 5 likes
  • 2 in conversation