topic Re: Manipulate proc contents in ODS and Base Reporting

Manipulate proc contents

anoopm7 — Fri, 11 Jul 2014 15:22:51 GMT

Hello,

I have around 1000 data sets and I am trying to write a code to create a proc contents for all these data sets and once I have them my second step would be to check for specific fields like account_number,DOB,sex etc and if they are available in the proc contents my second step should filter those datasets and write them to output.

Example :- I have datasets A,B,C,D,E and now I write a macro to do proc contents on each of A,B,C,D,E datasets and I create a separate output for each of them. Now my second step of code need to see if each of the new datasets created (i.e the output of the proc contents) have a set of fields in them and if they have the field then the second step should write them out.

Do any one have thoughts on the best way of doing it?

Re: Manipulate proc contents

jakarman — Fri, 11 Jul 2014 15:26:50 GMT

Use the sashelp.v- members as view to them all. But be aware the processing can be slow.

Re: Manipulate proc contents

data_null__ — Fri, 11 Jul 2014 15:40:09 GMT

If the data sets are all in the same data libraray you can get PROC CONTENTS on all of them with DATA=libname._ALL_; You will want NOPRINT and OUT= option with KEEP=data set option to keep only the variables in the OUT= data that you are interested in like MEMNAME, NAME, TYPE, LENGTH etc.

You may need other or perhaps all of them depending on what you are doing.

Re: Manipulate proc contents

PaigeMiller — Fri, 11 Jul 2014 15:41:08 GMT

Don't you mean SASHELP.VCOLUMN instead of SASHELP.VMEMBER?

Re: Manipulate proc contents

jakarman — Fri, 11 Jul 2014 15:56:23 GMT

And sashelp.vtable .... I yes but was too lazy

Re: Manipulate proc contents

stat_sas — Fri, 11 Jul 2014 16:25:08 GMT

Hi,

Suppose your all datasets are in the same folder then following may generate the desired results.

proc contents data=SASHELP._all_ out=vars(keep=memname name); /* used SASHELP as an example */
run;

proc sql;
select distinct memname from vars
where name in ('A','B','C'); /* Fields to be searched */
quit;

Re: Manipulate proc contents

Tom — Fri, 11 Jul 2014 16:58:58 GMT

So if you have the list of variable names then a single query against DICTIONARY.COLUMNS should get the list of datasets that contain all of the variables.

%let varlist='NAME','AGE';

%let nvars=2 ;

proc sql ;

select distinct catx('.',libname,memname) as dsname

from dictionary.columns

where libname='SASHELP'

and upcase(name) in (&varlist)

group by 1

having count(*) = &nvars

;

quit;

Re: Manipulate proc contents

anoopm7 — Fri, 11 Jul 2014 17:21:16 GMT

I wrote the below basic sas code to start with (as given below). This filters out the variables names and then it checks if specific variables are available and if they are available if writes the output. So from the title name I can identify the file name and that in turn help me to identify which dataset has these variables

CHALLENGE :- The datasets that I need to scan are spread across multiple folders and across multiple years. For example as below

Dataset :- /june/data/<port>/<rep>/<year>/<month>/<sasfile name>

here port,rep,year,month,sasfile name all are different. I could go define each of the files but that is going to take me a long time. Is there some technique that would allow me to penetrate inside the folders?

--------

SAS CODE USED

--------

libname rpl "/june/dev/adhoc/test/SASEG" ;

libname check "/june/dev/adhoc/test/SASEG" ;

%macro x(port);

libname bbr "/june/data/&port./repor1/2014/06/";

proc contents data=bbr.file1 noprint out=rpl.&port. (keep=name);

run ;

data check.&port. ;

set rpl.&port. ;

if NAME in ('ACCOUNT','CCSTAT','ZIPCODE') ;

run ;

proc print data= check.&port. ;

title check.&port. ;

run ;

%mend ;

%x(zone1);

%x(zone2);

%x(zone3);

Re: Manipulate proc contents

Reeza — Fri, 11 Jul 2014 18:36:37 GMT

Yes, you can use standard OS commands.

Google Recursively list all files in a directory in (YOUR OS)

Here's a link for Unix:

https://kb.iu.edu/d/abef

Re: Manipulate proc contents

PaigeMiller — Fri, 11 Jul 2014 19:05:21 GMT

You don't need PROC CONTENTS or what follows. You simply need to issue the appropriate LIBNAME statements. Then use the solution by Tom in message 6 of this thread, probably without

libname='SASHELP'.

Re: Manipulate proc contents

jakarman — Fri, 11 Jul 2014 19:11:08 GMT

Several ways::

1/ You can use OS commands when the installation/configuration has been set to allow you.
In this case you can use an Unix "ls -latr" command with redirecting to analyse that file building up a dataset of folders.
When you have access to the system doing that "ls"" command but not being allowed to run that from SAS you split that up in several steps

2/ Build your SAS dataset with OS folder using SAS functions. It will be a lot of work

1 and 2/ Proceed with that SAS dataset creating the associated libnamess Put some counter in the libname def (max string length 😎

3/ Build all libnames using some macro-looping. An optimistic approach is allowed ignoring the failures of combinations that do not exist.

Having alle libnames defined use the SAShelp.Vcolumns dataset with your selection on column names.

All contents information will be automatic generated by SAS at the moment of accessing that view.

Re: Manipulate proc contents

anoopm7 — Fri, 11 Jul 2014 19:55:38 GMT

Sorry guys, have been trying to swim against the wave (don't hate me for not using sashelp yet !!)

let me tell you what I did so far...since the list of sas files were around 60,000 spread across multiple directories/filenames etc I decided to do a basic listing (LS) and created that as a .txt output file. Once I did that I read the .txt file at a particular position and hence my new output file just had the file names

the LS output

-rwxr-x--- 1 bag23 too8 56K Jul 1 11:40 /july/first/ny/fq/2014/06/jjssn.sas7bndx

-rwxr-x--- 1 bag23 too8 32K Jul 1 11:40 /july/first/ny/fq/2014/06/jjssn.sas7bdat

-rwxr-x--- 1 bag23 too8 56K Jul 1 11:40 /july/first/ny/fq/2014/06/jjpii.sas7bndx

-rwxr-x--- 1 bag23 too8 32K Jul 1 11:40 /july/first/ny/fq/2014/06/jjpii.sas7bdat

-rwxr-x--- 1 bag23 too8 56K Jul 1 11:40 /july/first/ny/fq/2014/06/jj.sas7bndx

NEW FILE output after selecting using the new code

/july/first/ny/fq/2014/06/jjssn.sas7bndx

/july/first/ny/fq/2014/06/jjssn.sas7bdat

/july/first/ny/fq/2014/06/jjpii.sas7bndx

/july/first/ny/fq/2014/06/jjpii.sas7bdat

/july/first/ny/fq/2014/06/jj.sas7bndx

now I want to read this NEW FILE such that I could get the proc print of each of these records..i.e read a record from the NEW FILE and then PRINT the proc content of first record and then read next record print the PROC CONTENT of that etc..

Re: Manipulate proc contents

jakarman — Fri, 11 Jul 2014 20:18:02 GMT

You cannot read those file *.sas7- files as text files. The way to read them is by library references that are on the folder names.

You are need the tree of folders without any files on the OS level. The a attribute in: "ls -latr" I inlcuding all hidden files witn the . (current folder) an .. (upper level folder)
The bndx files types are indexfiles associated with a dataset with the same name. Some more types are possible.

The folders have an other indication at the file attributes, starting with the letter d (directory).

Go for the folders not files and connect them to libnames.

Re: Manipulate proc contents

Peter_C — Fri, 11 Jul 2014 21:34:14 GMT

I would use a data step to parse out the paths which hold sas datasets into a libs dataset. Then

data ;

Set libs;

By path;

If last.path ;

CALL EXECUTE( 'libname L ' !! Quote(trim( path)) !!' ;' ) ;

CALL EXECUTE( 'PROC CONTENTS DATA=L._ALL_ NOPRINT OUT=A; ' ) ;

CALL EXECUTE( data b; set a; ');

call execute(' length path $300 ; retain path ' !! quote( trim( path )) );

call execute(' ; run; proc append base= collection data= b ; run ; ' ) ;

Run ;

That provides a work.collection detailling all columns along with the path.

you might want to drop columns you don't need.

Re: Manipulate proc contents

Reeza — Fri, 11 Jul 2014 21:58:25 GMT

There's a libname function in SAS, as well as the fact that the libname statement will take multiple arguments.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

libname test ('C:\Data\StatCan' ,

'C:\Data\StatCan\ShapeToTab');

Re: Manipulate proc contents

Tom — Fri, 11 Jul 2014 23:11:22 GMT

I have totally lost the big picture of what you want to do. Originally it had something to do with testing which datasets had particular variables.

If you are talking about looking at the contents of tens of thousands of SAS datasets and then printing the data in those files? That does not make much sense as who is going to read all of that output.

Now if you are talking about making a report that summarizes each dataset then it might make more sense. Say something like

FILENAME NOBS VAR1 VAR2 VAR3

/july/first/ny/fq/2014/06/jjssn.sas7bdat 1,212 YES NO NO

/july/first/ny/fq/2014/06/jjpii.sas7bdat 512 YES YES YES

Re: Manipulate proc contents

Peter_C — Mon, 14 Jul 2014 19:31:40 GMT

I thought the query was to report the tables which contained certain columns.

My proposal supports that query by creating a table with the full library contents info with all column info + path, for each folder of sas datasets.

The next stage would filter to show only rows for those columns required. And then summarise whatever.

A libname concatenating all paths will hide a table in a subfolder a/b/c/ if a table of the same name is also in a higher level folder like a/b/.

Re: Manipulate proc contents

jakarman — Tue, 15 Jul 2014 05:32:52 GMT

Peter, I would also not suggest concatenation of libraries in this moment. It is excellent with version management using catalog member types (eg formats) but not for data-processing.

The op's question is looking like data investigation on a environment where a lot of segregated SAS datasets have been created in a lot of folders.
As the perception on folders and SAS datasets with Unix is not correct it is not the experienced SAS analyst, may be a new one taken over work, a new technical person or an auditor.

Re: Manipulate proc contents

Peter_C — Tue, 15 Jul 2014 07:25:59 GMT

Jaap

you describe a suituation I find when arriving at a new client.

It is really handy to register contents of all atasets in the environment.

Then I become aware of terminology that the "locals" use for specific keys and other attributes. For the same reason I list all .sas code files then parse macro names of macros.

Usually it finds more overlap than justified by version history.

It isn't as good as a full impact analysis but might have more scope.

Peter.c

Re: Manipulate proc contents

anoopm7 — Tue, 15 Jul 2014 16:13:59 GMT

My apologies if I am not clear.. Let me explain it with some sample codes

The below is the code

libname rpl "/temp/test/SASEG" ;

libname check "/temp/test/SASEG" ;

filename dd "/temp/test/SASEG/sample.txt" ;

data test;

infile dd missover;

input @54 filename : $80. ;

run;

proc print data=test;

run;

****The out put of the test using the above proc print is below.

QUESTION

1) I have such a SAS dataset (a list of datasets). Now how do I use that SAS dataset to do a PROC CONTENTS on each of the observation in that file?

2) If that could be done how should I write it down as a output ?

3) Then I would check the output of the proc contents to see if they have some variables that Iam interested and if the variables are present that I want to report that dataset.

Obs	filename
1	/july/q2/bc/ringreport/2014/06/bankcard.sas7bndx
2	/july/q2/bc/ringreport/2014/06/bankcard.sas7bdat
3	/july/q2/bc/ringreport/2014/06/bank_co.sas7bndx
4	/july/q2/bc/ringreport/2014/06/bank_co.sas7bdat
5	/july/q2/bc/ringreport/2014/06/multran.sas7bndx
6	/july/q2/bc/ringreport/2014/06/multran.sas7bdat
7	/july/q2/avco/ringreport/2014/06/avco.sas7bndx
8	/july/q2/avco/ringreport/2014/06/avco.sas7bdat
9	/july/q2/citiusa/ringreport/2014/06/bankcard.sas7bndx
10	/july/q2/citiusa/ringreport/2014/06/bankcard.sas7bdat
11	/july/q2/citiusa/ringreport/2014/06/bank_co.sas7bndx
12	/july/q2/citiusa/ringreport/2014/06/bank_co.sas7bdat
13	/july/q2/acb/ringreport/2014/06/acb.sas7bndx
14	/july/q2/acb/ringreport/2014/06/acb.sas7bdat
15	/july/q2/comm/ringreport/2014/06/coml.sas7bndx
16	/july/q2/comm/ringreport/2014/06/coml.sas7bdat
17	/july/q2/comm/ringreport/2014/06/coml_co.sas7bndx
18	/july/q2/comm/ringreport/2014/06/coml_co.sas7bdat
19	/july/q2/avco/ringreport/2014/06/chdi.sas7bndx
20	/july/q2/avco/ringreport/2014/06/chdi.sas7bdat
21	/july/q2/avco/ringreport/2014/06/chda.sas7bndx
22	/july/q2/avco/ringreport/2014/06/chda.sas7bdat
23	/july/q2/citiusa/ringreport/2014/06/chda.sas7bndx
24	/july/q2/citiusa/ringreport/2014/06/chda.sas7bdat
25	/july/q2/citiusa/ringreport/2014/06/chdi.sas7bndx
26	/july/q2/citiusa/ringreport/2014/06/chdi.sas7bdat
27	/july/q2/acb/ringreport/2014/06/chda.sas7bndx
28	/july/q2/acb/ringreport/2014/06/chda.sas7bdat
29	/july/q2/acb/ringreport/2014/06/chdi.sas7bndx
30	/july/q2/acb/ringreport/2014/06/chdi.sas7bdat
31	/july/q2/comm/ringreport/2014/06/chda.sas7bndx
32	/july/q2/comm/ringreport/2014/06/chda.sas7bdat
33	/july/q2/comm/ringreport/2014/06/chdi.sas7bndx
34	/july/q2/comm/ringreport/2014/06/chdi.sas7bdat
35	/july/q2/rpl/ringreport/2014/06/chdi.sas7bndx
36	/july/q2/rpl/ringreport/2014/06/chdi.sas7bdat
37	/july/q2/rpl/ringreport/2014/06/chda.sas7bndx
38	/july/q2/rpl/ringreport/2014/06/chda.sas7bdat
39	/july/q2/rpl/ringreport/2014/06/cardseg.sas7bdat
40	/july/q2/bestbuy/ringreport/2014/06/chdi.sas7bndx
41	/july/q2/bestbuy/ringreport/2014/06/chdi.sas7bdat
42	/july/q2/bestbuy/ringreport/2014/06/chda.sas7bndx
43	/july/q2/bestbuy/ringreport/2014/06/chda.sas7bdat
44	/july/q2/bestbuy/ringreport/2014/06/cardseg.sas7bdat
45	/july/q2/thd/ringreport/2014/06/chdi.sas7bndx
46	/july/q2/thd/ringreport/2014/06/chdi.sas7bdat
47	/july/q2/thd/ringreport/2014/06/chda.sas7bndx
48	/july/q2/thd/ringreport/2014/06/chda.sas7bdat
49	/july/q2/thd/ringreport/2014/06/cardseg.sas7bdat
50	/july/q2/sears1/ringreport/2014/06/chdi.sas7bndx
51	/july/q2/sears1/ringreport/2014/06/chdi.sas7bdat
52	/july/q2/sears1/ringreport/2014/06/chda.sas7bndx
53	/july/q2/sears1/ringreport/2014/06/chda.sas7bdat
54	/july/q2/sears1/ringreport/2014/06/cardseg.sas7bdat