topic Re: Create data set with names of data sets include name in SAS Programming

Create data set with names of data sets include name

Ronein — Wed, 01 Jan 2020 07:03:18 GMT

Hello

There are 7 data sets in work library called :

OFFERS190815

OFFERS190820

OFFERS190825

OFFERS190902

OFFERS190905

OFFERS190910

OFFERS190915

I run this code in order to get list of datasets that start with "OFFERS" .

I don't know why but the output dataset called "CONTENT" has 70 rows and not 7.

(Each data set name appears 10 times and I have 7 data sets so there are 70 rows)

proc contents data=work._all_  noprint out=content 
(keep= memname where=(substr(upcase(memname),1,6) = 'OFFERS'));
run;

Re: Create data set with names of data sets include name

unison — Wed, 01 Jan 2020 07:20:53 GMT

How many variable names are in each dataset? 10? Try keeping “name” as well to inspect.

I’d use DICTIONARY.TABLES for this using proc sql.

Re: Create data set with names of data sets include name

Ksharp — Wed, 01 Jan 2020 10:39:05 GMT

Try EQT operator.

proc contents data=work._all_  noprint out=content 
(keep= memname where=( upcase(memname) eqt 'OFFERS'));
run;

Re: Create data set with names of data sets include name

novinosrin — Wed, 01 Jan 2020 11:56:48 GMT

Hi @Ronein When you use PROC CONTENTS _all_ , the OUT= would account for all descriptor portion in the dataset. What this means is, suppose you have 7 variables in each of your dataset, the metadata is stored like

DATASET1 VAR1

DATASET1 VAR2

DATASET1 VAR3

And so on. Therefore the dataset name happens to have a duplicate record in order to account for each entry of a variable name. Hence, your where=(substr(upcase(name),1,6) = 'OFFERS')) would still result in Duplicates corrresponding to the number of variables the dataset contains.

So ideally you should be using DICTIONARY. TABLES or its view SASHELP.VTABLE. Albeit, you could still make PROC CONTENTS to work by resortiing to ODS OUTPUT objects that basically writes the printed message to an output dataset.

Example,

ODS OUTPUT members=content(keep= name where=(substr(upcase(name),1,6) = 'OFFERS')); ;
proc contents data=work._all_   nods   ;
run;

Re: Create data set with names of data sets include name

Ronein — Wed, 01 Jan 2020 12:13:14 GMT

Yes!! There are 10 varaibles in each data set .

I just want to get a list of the data sets start with name "OFFER".

What is the problem that I get #datasets X #varaibles in the output data set?

Re: Create data set with names of data sets include name

hashman — Wed, 01 Jan 2020 18:05:54 GMT

@Ronein:

Using proc CONTENTS is tantamount to reading DICTIONARY.COLUMNS - that is, it gets the metadata for both the tables and the variables - so you get a row for each variable for each table whose name passes the filter. Since you only need to get the distinct table names (i.e. metadata at the member level), CONTENTS is a wrong tool - you need to access DICTIONARY.TABLES instead, and it can be done directly only using SQL:

proc sql ;                                                                                                                              
  create table content as select memname from dictionary.tables where upcase (memname) eqt "OFFERS" ;                                   
quit ;

Indirectly, you can also access the SASHELP.VTABLE, which can also be used as input in the DATA step and procedures other than SQL, for example:

data content ;                                                                                                                          
  set sashelp.vtable ;                                                                                                                  
  where upcase (memname) =: "OFFERS" ;                                                                                                  
run ;

However, I wouldn't recommend it, as accessing DICTIONARY.TABLES is cleaner, faster, and doesn't pollute the log with extraneous info.

Kind regards

Paul D.

Re: Create data set with names of data sets include name

Tom — Wed, 01 Jan 2020 21:06:37 GMT

The output of PROC CONTENTS is one observation per VARIABLE. Just add another step to eliminate the replicated member names.

proc sort data=content nodupkey;
  by memname;
run;