topic Re: Find csv datasets with birthday or dob variables and create a report. Filenames have with space in SAS Programming

Find csv datasets with birthday or dob variables and create a report. Filenames have with space

CHL0320 — Mon, 25 Oct 2021 21:34:14 GMT

Hi Community,

I would like to see if there are variables in the dataset that have a birthday or dob information in them. The input datasets are in CSV and there are more than 100K files. The following code seems fine if the input filename has no space or special character in it. However, there are many original filenames with space or special characters as the example below, fij k-1, so the code cannot work. I need help to tackle it as the original names in CSV don't allow to alter.

Thank you for your help.

%Macro CSV(filename);
options obs=5;
options validvarname=any;
options validmemname=extend;
proc import out=&filename
datafile="&dir\&filename..csv"
dbms=csv;
run;

proc sql;
create table keep as
select memname, name ,format
from dictionary.columns
where libname='WORK' and
(
(index(upcase(name),'BIRTH')>0) or
(index(upcase(name),'DOB')>0))

AND

((index(format, 'MMDDYY')>0) or
(index(format, 'DATE')>0))

;
quit;

data list;
set keep;
Directoryname="&dir";
rename memname=Filename name=Variable;
run;

%Mend CSV;

%let dir=C:\abc\ef g
;

%CSV(fij k-1)

Re: Find csv datasets with birthday or dob variables and create a report. Filenames have with space

ballardw — Mon, 25 Oct 2021 22:11:34 GMT

Search the forum for reading lists of files using operating system tools such as the DIR (windows) or LS command (Unix/Linux) so that you have a complete path/filename as one variable.

Some :

https://communities.sas.com/t5/SAS-Programming/macro-for-load-in-multiple-files-csv/m-p/763795

https://communities.sas.com/t5/SAS-Programming/Help-merging-multiple-CSV-files-to-a-dataset/m-p/184804

Or if there are some rules for building the names...

If all the files (or possibly even most) have the same structure there are ways to read them using wildcards in the names.

BTW:

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Typically debugging macros involves setting Options MPRINT; and possibly SYMBOLGEN and MLOGIC depending on the type of code generation failure you are having.

Re: Find csv datasets with birthday or dob variables and create a report. Filenames have with space

Reeza — Mon, 25 Oct 2021 22:29:20 GMT

I wouldn't bother with this portion:

((index(format, 'MMDDYY')>0) or
(index(format, 'DATE')>0))

If you're using PROC IMPORT to import the data it could be character or datetime because you're not specifying variable types. Do you only want SAS dates? If so, look at the FMTINFO() function instead to get all date formats.

@CHL0320 wrote:

Hi Community,

I would like to see if there are variables in the dataset that have a birthday or dob information in them. The input datasets are in CSV and there are more than 100K files. The following code seems fine if the input filename has no space or special character in it. However, there are many original filenames with space or special characters as the example below, fij k-1, so the code cannot work. I need help to tackle it as the original names in CSV don't allow to alter.

Thank you for your help.

%Macro CSV(filename);
options obs=5;
options validvarname=any;
options validmemname=extend;
proc import out=&filename
datafile="&dir\&filename..csv"
dbms=csv;
run;

proc sql;
create table keep as
select memname, name ,format
from dictionary.columns
where libname='WORK' and
(
(index(upcase(name),'BIRTH')>0) or
(index(upcase(name),'DOB')>0))

AND

((index(format, 'MMDDYY')>0) or
(index(format, 'DATE')>0))

;
quit;

data list;
set keep;
Directoryname="&dir";
rename memname=Filename name=Variable;
run;

%Mend CSV;

%let dir=C:\abc\ef g
;

%CSV(fij k-1)

Re: Find csv datasets with birthday or dob variables and create a report. Filenames have with space

Kurt_Bremser — Tue, 26 Oct 2021 06:44:20 GMT

I would go about this differently.

Find files with a .csv extension. Might be simple or a little complex if you need to search multiple directories.
Read the header line of each file
Scan it for names indicating a date

I would not rely at all on the formats set by the guessing of PROC IMPORT.