topic Re: Loop over all .cpt files in a directory and export in SAS Programming

Loop over all .cpt files in a directory and export

otteheng — Fri, 11 Jun 2021 15:46:35 GMT

I have directory with a couple hundred .cpt files that I would like to export as sas7bdat files into a new directory. I am using SAS Viya V.03.05 (SAS Studio Enterprise).

Currently I have this:

cas cta_session;
	caslib _all_ assign;

filename cptfile filesrvc folderpath='/Users/REMOVED/cptfiles' name='zcta_counties.cpt';

libname zipfiles '/home/REMOVED';
proc cimport infile=cptfile library=zipfiles; 
run;

I could replace the name='FILE-NAME.cpt one by one but given how many files I have the process is a bit time intensive.

Is there a way to loop through every .cpt in my folderpath?

Re: Loop over all .cpt files in a directory and export

Reeza — Fri, 11 Jun 2021 15:56:33 GMT

Do you have a naming convention or a list of files or do you need to create that list of files?

You're likely going into macro territory so here are some links that will be helpful

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage (from documentation)
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Re: Loop over all .cpt files in a directory and export

jimbarbour — Fri, 11 Jun 2021 16:25:01 GMT

Have you looked at the FILEVAR option? If you can create a list of the files you want (maybe an ls -l *.cpt with the output directed to a text file), you should be able to read that list into SAS and then use the FILEVAR option to read the files themselves.

Jim

P.S. I'm not currently working on UNIX. I put ls -l because that's what I remember off the top of my head, but I'm sure there's a better option than -l to produce exactly what you want without superfluous information.

Re: Loop over all .cpt files in a directory and export

otteheng — Fri, 11 Jun 2021 16:41:13 GMT

Most of the files are "zipcode_xxxx.cpt" with some of the files including a version number i.e. "zipcode_xxxx_vx.cpt". The first two x's are jan, apr, jul, oct and the last two xx's are dates 11,12,13,14, etc.

I had a similar issue when the downloaded files were in .zip format. I used R to unzip the files into a directory of my choosing.

#1.1: Unzip files

    #A. Grab directory of zip files
    file.name.zip <- dir(raw_data_path, pattern=".zip")
    
    #B. Loop through directory
    for (zzz in file.name.zip) {
      print(zzz)
      unzip(paste(raw_data_path, zzz, sep="\\"),exdir=new_data_path)
    }

Does SAS have a function, similar to dir(raw_data_path, pattern=".zip"), that creates a list of values found in a directory given a file pattern? For instance I would want to replace the previous function ".zip" with ".cpt".

Re: Loop over all .cpt files in a directory and export

jimbarbour — Fri, 11 Jun 2021 16:54:43 GMT

You can do a FILENAME My_File PIPE "ls -l *.cpt"; Again, though, I'm not sure that "L" is the best option. I think there's an option that is going to just give you the filenames without the size, date, etc. I'm just not remembering which option that is. Let me see if I can work up a little example.

Jim

Re: Loop over all .cpt files in a directory and export

Kurt_Bremser — Fri, 11 Jun 2021 17:02:41 GMT

@otteheng is working off the CAS file service, so UNIX commands won't help here.

I don't know if CAS provides functions analog to DOPEN, DNUM and DREAD for the file service.

Re: Loop over all .cpt files in a directory and export

Reeza — Fri, 11 Jun 2021 17:10:55 GMT

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/n0js70lrkxo6uvn1fl4a5aafnlgt.htm
See the documentation I linked to, again not sure if it will work with CAS but if you have a pattern it's quite straightforward.

Re: Loop over all .cpt files in a directory and export

jimbarbour — Fri, 11 Jun 2021 17:19:34 GMT

@Kurt_Bremser, can a SAS dataset be created apart from CAS and then the SAS dataset brought in? The FILENAME PIPE is such a useful tool that it will be hard to part with when our shop switches to Viya -- if indeed there is no way to use it. 😞

Example from Windows, below. I'm searching for .log files, but any file extension could be substituted.

FILENAME	My_File	PIPE	"dir I:\commercial\production\OPSI\logs\sub_process_logs /a-d /b *.log";

DATA	My_Files;
	INFILE	My_File	TRUNCOVER;
	INPUT	Filename	$CHAR512.;
RUN;

Jim

Re: Loop over all .cpt files in a directory and export

Kurt_Bremser — Fri, 11 Jun 2021 20:12:10 GMT

I see nothing in the documentation that says PIPE does not work in CAS, but I don't know how/if you can access the physical filesystem, or if SAS Studio for Viya can be made to upload files to a physical location instead of a place in the file service.