Hi all,
I am fairly new to SAS. I am trying to import multiple csv files (all in the same format), under the same directory, and creates new dataset for each file imported. So each dataset would have the same name as the file imported , e.g. Jan.csv into Jan.sas7bdat. I did some research online and tried to copy the macro. But it did not seemed to work and I am not sure where the problem is. Thank in advance for your help.
options merror mlogic mprint symbolgen spool;
%macro drive(dir,ext);
%local filrf rc did memcnt name i;
/* Assigns a fileref to the directory and opens the directory */
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));
/* Loops through entire directory */
%do i = 1 %to %sysfunc(dnum(&did));
/* Retrieve name and import each Excel file */
%let name=%qsysfunc(dread(&did,&i));
DATA mylib.&name;
LENGTH
FYBK 3
FYDBN $ 10
FYCOMN $ 35
FYCODD $ 20
FYCOID $ 10
FYSECC $ 3;
FORMAT
FYBK BEST1.
FYDBN $CHAR8.
FYCOMN $CHAR35.
FYCODD $CHAR20.
FYCOID $CHAR10.
FYSECC $CHAR3.;
INFORMAT
FYBK BEST1.
FYDBN $CHAR8.
FYCOMN $CHAR35.
FYCODD $CHAR20.
FYCOID $CHAR10.
FYSECC $CHAR3.;
INFILE "L:\auditing\auditing\DA\Test\2022\Data\&name"
ENCODING="WLATIN1"
DLM=','
MISSOVER
DSD ;
INPUT
FYBK : BEST1.
FYDBN : $CHAR8.
FYCOMN : $CHAR35.
FYCODD : $CHAR20.
FYCOID : $CHAR10.
FYSECC : $CHAR3.;
RUN;
%end;
/* Closes the directory and clear the fileref */
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));
%mend drive;
/* First parameter is the directory of where your files are stored. */
/* Second parameter is the extension you are looking for. */
%drive(L:\auditing\auditing\DA\Test\2022\Data,csv);
Here is the log.
1 The SAS System 10:53 Friday, October 14, 2022
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Test(A)_1014_00b_Import_DataStep_Test';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='L:\auditing\auditing\DA\Test\SAS Script\ACH_10142022(A).egp';
6 %LET _CLIENTPROJECTNAME='ACH_10142022(A).egp';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=PNG;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 /************************************************************************************************************************
24 ! *********************************
25 Purpose: Import all csv files under "Data" folder and creat a dataset ACHComb
26 Input: csv file from each month under .../Data folder
27 Output: mylib.ACHComb
28 Note:
29 1) All the date fields were read-in as character fields because the format varies; there is a separate script to convert
29 ! all these fields into proper date
30 format ("Test(A)_0928_01_Date Format" script)
31 *************************************************************************************************************************
31 ! *********************************/
32 options merror mlogic mprint symbolgen spool;
33 %macro drive(dir,ext);
34 %local filrf rc did memcnt name i;
35
36 /* Assigns a fileref to the directory and opens the directory */
37 %let rc=%sysfunc(filename(filrf,&dir));
38 %let did=%sysfunc(dopen(&filrf));
39
40 /* Loops through entire directory */
41 %do i = 1 %to %sysfunc(dnum(&did));
42
43 /* Retrieve name and import each Excel file */
44 %let name=%qsysfunc(dread(&did,&i));
45
46 DATA mylib.&name;
47 LENGTH
48 FYBK 3
49 FYDBN $ 10
50 FYCOMN $ 35
51 FYCODD $ 20
52 FYCOID $ 10
53 FYSECC $ 3;
132
133 FORMAT
134 FYBK BEST1.
135 FYDBN $CHAR8.
136 FYCOMN $CHAR35.
137 FYCODD $CHAR20.
138 FYCOID $CHAR10.
139 FYSECC $CHAR3.;
218 INFORMAT
219 FYBK BEST1.
220 FYDBN $CHAR8.
221 FYCOMN $CHAR35.
222 FYCODD $CHAR20.
223 FYCOID $CHAR10.
224 FYSECC $CHAR3.;
303 INFILE "L:\auditing\auditing\DA\Test\2022\Data\&name"
304 ENCODING="WLATIN1"
305 DLM=','
306 MISSOVER
307 DSD ;
308 INPUT
309 FYBK : BEST1.
310 FYDBN : $CHAR8.
311 FYCOMN : $CHAR35.
312 FYCODD : $CHAR20.
313 FYCOID : $CHAR10.
314 FYSECC : $CHAR3.;
393 RUN;
394
395 %end;
396
397 /* Closes the directory and clear the fileref */
398 %let rc=%sysfunc(dclose(&did));
399 %let rc=%sysfunc(filename(filrf));
400 %mend drive;
401
402 /* First parameter is the directory of where your files are stored. */
8 The SAS System 10:53 Friday, October 14, 2022
403 /* Second parameter is the extension you are looking for. */
404 %drive(L:\auditing\auditing\DA\Test\2022\Data,csv);
MLOGIC(DRIVE): Beginning execution.
MLOGIC(DRIVE): Parameter DIR has value L:\auditing\auditing\DA\Test\2022\Data
MLOGIC(DRIVE): Parameter EXT has value csv
MLOGIC(DRIVE): %LOCAL FILRF RC DID MEMCNT NAME I
MLOGIC(DRIVE): %LET (variable name is RC)
SYMBOLGEN: Macro variable DIR resolves to L:\auditing\auditing\DA\Test\2022\Data
MLOGIC(DRIVE): %LET (variable name is DID)
SYMBOLGEN: Macro variable FILRF resolves to #LN00015
SYMBOLGEN: Macro variable DID resolves to 1
MLOGIC(DRIVE): %DO loop beginning; index variable I; start value is 1; stop value is 10; by value is 1.
MLOGIC(DRIVE): %LET (variable name is NAME)
SYMBOLGEN: Macro variable DID resolves to 1
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable NAME resolves to October.csv
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "NAME".
404 mylib.October.csv
______
22
201
MPRINT(DRIVE): DATA mylib.October.csv;
MPRINT(DRIVE): LENGTH FYBK 3 FYDBN $ 10 FYCOMN $ 35 FYCODD $ 20 FYCOID $ 10 FYSECC $ 3;
SYMBOLGEN: Macro variable NAME resolves to October.csv
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(DRIVE): INFILE "L:\auditing\auditing\DA\Test\2022\Data\October.csv" ENCODING="WLATIN1" DLM=',' MISSOVER DSD ;
MPRINT(DRIVE): INPUT FYBK : BEST1. FYDBN : $CHAR8. FYCOMN : $CHAR35. FYCODD : $CHAR20. FYCOID : $CHAR10. FYSECC : $CHAR3. ;
MPRINT(DRIVE): RUN;
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.
ERROR 201-322: The option is not recognized and will be ignored.
ERROR: Libref OCTOBER is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MLOGIC(DRIVE): %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(DRIVE): %LET (variable name is NAME)
SYMBOLGEN: Macro variable DID resolves to 1
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable NAME resolves to August.csv
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "NAME".
404 mylib.August.csv
______
22
201
MPRINT(DRIVE): DATA mylib.August.csv;
MPRINT(DRIVE): LENGTH FYBK 3 FYDBN $ 10 FYCOMN $ 35 FYCODD $ 20 FYCOID $ 10 FYSECC $ 3 FYCED $ 10 FYEED $ 6 FYSDJ $ 3 FYODFI 8 ;
SYMBOLGEN: Macro variable NAME resolves to August.csv
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(DRIVE): INFILE "L:\auditing\auditing\DA\Test\2022\Data\August.csv" ENCODING="WLATIN1" DLM=',' MISSOVER DSD ;
MPRINT(DRIVE): INPUT FYBK : BEST1. FYDBN : $CHAR8. FYCOMN : $CHAR35. FYCODD : $CHAR20. FYCOID : $CHAR10. FYSECC : $CHAR3. ;
MPRINT(DRIVE): RUN;
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.
ERROR 201-322: The option is not recognized and will be ignored.
ERROR: Libref AUGUST is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MLOGIC(DRIVE): %DO loop index variable I is now 11; loop will not iterate again.
MLOGIC(DRIVE): %LET (variable name is RC)
SYMBOLGEN: Macro variable DID resolves to 1
MLOGIC(DRIVE): %LET (variable name is RC)
MLOGIC(DRIVE): Ending execution.
405
406 GOPTIONS NOACCESSIBLE;
407 %LET _CLIENTTASKLABEL=;
408 %LET _CLIENTPROCESSFLOWNAME=;
409 %LET _CLIENTPROJECTPATH=;
410 %LET _CLIENTPROJECTNAME=;
411 %LET _SASPROGRAMFILE=;
412
413 ;*';*";*/;quit;run;
414 ODS _ALL_ CLOSE;
415
416
417 QUIT; RUN;
418
Click image to register for webinar
