topic Re: Want to convert csv files to sas7bdat from folder in SAS Programming

Want to convert csv files to sas7bdat from folder

noda6003 — Tue, 01 Feb 2022 10:02:47 GMT

I want to convert all csv files in a folder to sas7bdat and have the names same as it is in csv file, I found a program but it doesnt work. any help on it.

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name;

   %let cnt=0;
   %let filrf=mydir;

   %let rc=%sysfunc(filename(filrf,&dir));
   %let did=%sysfunc(dopen(&filrf));

   %if &did ne 0 %then %do;
      %let memcnt=%sysfunc(dnum(&did));
      %do i=1 %to &memcnt;
         %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
            %if %superq(ext) = %superq(name) %then %do;
               %let cnt=%eval(&cnt+1);
               %put %qsysfunc(dread(&did,&i));
               proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
                  dbms=csv replace;
               run;
            %end;
         %end;
       %end;
    %end;
  %else %put &dir cannot be opened.;

  %let rc=%sysfunc(dclose(&did));
%mend drive;

%drive(C:/let,csv);

How can i replace them with dataset as it is in csv and place in same folder as csv

Re: Want to convert csv files to sas7bdat from folder

LinusH — Tue, 01 Feb 2022 10:21:49 GMT

You have to specify a SAS libref in the out= option that points to the same location as where your Excel files resides.

Re: Want to convert csv files to sas7bdat from folder

Kurt_Bremser — Tue, 01 Feb 2022 12:11:09 GMT

I would recommend against having non-SAS files in a directory that is used as a SAS library. Instead do a tree like

- import
- - saslib
- - excel_files

Put the spreadsheets into excel_files, and run the LIBNAME for saslib.

You can still move/copy/delete everything through the parent directory import.

Re: Want to convert csv files to sas7bdat from folder

noda6003 — Tue, 01 Feb 2022 14:22:27 GMT

I have created libname but not sure how to make same dataset names as in csv in out=

Re: Want to convert csv files to sas7bdat from folder

Kurt_Bremser — Tue, 01 Feb 2022 14:51:04 GMT

To direct the output of the IMPORT procedure to a library other than WORK, you need to tell it so in the OUT= option of the PROC IMPORT statement and use a two-level name for the dataset there.

Re: Want to convert csv files to sas7bdat from folder

noda6003 — Tue, 01 Feb 2022 14:59:38 GMT

sorry for asking again, but i have multiple csv files so i am not sure how to get same names of csv to sas7bdat

Re: Want to convert csv files to sas7bdat from folder

Kurt_Bremser — Tue, 01 Feb 2022 15:13:39 GMT

See the changes/additions in this code piece:

      %do i=1 %to &memcnt;
         %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
            %if %superq(ext) = %superq(name) %then %do;
               %let fname = %scan(%qsysfunc(dread(&did,&i)),1,.);
               %put %qsysfunc(dread(&did,&i));
               proc import
                 datafile="&dir\%qsysfunc(dread(&did,&i))"
                 out=libname.&fname. 
                 dbms=csv
                 replace
               ;
               run;
            %end;
         %end;
       %end;

It assumes that there will be only one dot in the filename, separating the sheet name from the file extension (otherwise the name would be invalid for a dataset anyway). Replace "libname" with the name of your intended target library. Macro variable cnt is no longer used.

Re: Want to convert csv files to sas7bdat from folder

ballardw — Tue, 01 Feb 2022 15:14:08 GMT

@noda6003 wrote:

I want to convert all csv files in a folder to sas7bdat and have the names same as it is in csv file, I found a program but it doesnt work. any help on it.

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name;

   %let cnt=0;
   %let filrf=mydir;

   %let rc=%sysfunc(filename(filrf,&dir));
   %let did=%sysfunc(dopen(&filrf));

   %if &did ne 0 %then %do;
      %let memcnt=%sysfunc(dnum(&did));
      %do i=1 %to &memcnt;
         %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);
         %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
            %if %superq(ext) = %superq(name) %then %do;
               %let cnt=%eval(&cnt+1);
               %put %qsysfunc(dread(&did,&i));
               proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
                  dbms=csv replace;
               run;
            %end;
         %end;
       %end;
    %end;
  %else %put &dir cannot be opened.;

  %let rc=%sysfunc(dclose(&did));
%mend drive;

%drive(C:/let,csv);

How can i replace them with dataset as it is in csv and place in same folder as csv

Names of what? The SAS data set? Variables? Both have rules in SAS and may not be possible. SAS datasets and variable names are limited in the number of characters allowed: 32.

Also:

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.

Re: Want to convert csv files to sas7bdat from folder

Tom — Tue, 01 Feb 2022 15:31:03 GMT

What your rules for "have the names same as it is in csv file"?

Do you want to include the .csv in the dataset names? SAS names do not normally allow periods.

Let's assume you meant you wanted to remove the .csv from the end of the name.

What about names with other invalid characters, like hyphens or spaces?

What about names that start with a digit?

To remove the .csv once you have found it is simple. If you are positive there is only one period in the name just use SCAN() again with 1 instead of -1 as the index. Otherwise use SUBSTR() to to remove the last 4 characters (the length of &ext plus 1).