how to import multiple csv files with same format to sas with data step

[Tesera]

I tried to adjust it to my script, but I am beginner and this is too complicated for me.

I dont understand how this statement work: 

filename f3 "%sysfunc(pathname(work))/fileC_2019.csvxxx";

as I am not administrator of the computer in my work, and it gives me weird path which I cannot see. So I put this instead: filename f7 "\\PC0525\SAS_EG\IACS\*_2014.csv" but it gives me empty table in the output.

[yabwon]

Hi @Tesera ,

This part:

filename f1 "%sysfunc(pathname(work))/fileA_2008.csvxxx";
filename f2 "%sysfunc(pathname(work))/fileB_2009.csvxxx";
filename f3 "%sysfunc(pathname(work))/fileC_2019.csvxxx";

data _null_;
  file f1;
  put "a,b,c";
  put "1,2,3";
  put "4,5,6";
  file f2;
  put "a,b,c";
  put "7,8,9";
  put "10,11,12";
  file f3;
  put "a,b,c";
  put "13,14,15";
  put "16,17,18";
run;

was just to create example files.

The `%sysfunc(pathname(work))` basically gets the location of the work folder of the sas session. For your task only the second part is important and you are using the `infile` statement to read in your files.

This line of code

infile "%sysfunc(pathname(work))/*.csvxxx" dlm="," filename=F EOV=EOV Firstobs=2;

 points to my example files, you should change it to the directory you are using in your exercise.

All the best

Bart

[Tom]

---

@Tesera wrote:

I tried to adjust it to my script, but I am beginner and this is too complicated for me.

I dont understand how this statement work: 

filename f3 "%sysfunc(pathname(work))/fileC_2019.csvxxx";

as I am not administrator of the computer in my work, and it gives me weird path which I cannot see. So I put this instead: filename f7 "\\PC0525\SAS_EG\IACS\*_2014.csv" but it gives me empty table in the output.

---

Read the example solution more carefully.  That was the location that the program wrote the example data files it used to demonstrate.  Use the real path to the files you want to read.  Do your real filenames have an extension of csvxxx ?

[Tesera]

I put the end of the post this: "So I put this instead: filename f7 "\\PC0525\SAS_EG\IACS\*_2014.csv" but it gives me empty table in the output."

So I adjusted it to my path, but it didnt work the right way.

[yabwon]

@Tesera 

Could you share the code you used?

Bart

[Tesera]

My code looks like this, but it gives me NOTE: Invalid data for variable... etc.

filename f1 "\\PC0525\SAS_EG\IACS\*_2008.csv";
filename f2 "\\PC0525\SAS_EG\IACS\*_2009.csv";
filename f3 "\\PC0525\SAS_EG\IACS\*_2010.csv";
filename f4 "\\PC0525\SAS_EG\IACS\*_2011.csv";
filename f5 "\\PC0525\SAS_EG\IACS\*_2012.csv";
filename f6 "\\PC0525\SAS_EG\IACS\*_2013.csv";
filename f7 "\\PC0525\SAS_EG\IACS\*_2014.csv";


data 
%macro smallLoop1(s,e);
	%do year = &s. %to &e.;
	want&year.
	%end;
%mend smallLoop1;
%smallloop1(2008,2014)
wantOther
;

  length F $ 200;
  infile "\\PC0525\SAS_EG\IACS\*.csv" dlm="," filename=F EOV=EOV missover Firstobs=2 	DSD
		LRECL=32767;
  input @;
	/*infile '\\PC0525\SAS_EG\IACS\*.csv' */

if not EOV then do;

informat 
VS 12. 
DOTT $15. 
SUMAEU best12.	
DOPL_1 best12. 
;

format VS best12.;	
format DOTT	$15.;
format SUMAEU best12.;	
format DOPL_1 best12.;	

input 
VS 
DOTT $	
SUMAEU	
DOPL_1
;

select(compress(scan(F,-1,"\/"),,"KD"));
%macro smallLoop2(s,e);
%do year = &s. %to &e.;
when ("&year.") output want&year.;
%end;
%mend smallLoop2;
%smallLoop2(2008,2014)
otherwise output wantOther;
end;
end;
EOV = 0;
run;

I tried this:  

data PLAT_MODUL08az14;
	infile '\\PC0525\SAS_EG\IACS\*.csv'
delimiter=';'
		missover
		firstobs=2
		DSD
		LRECL=32767
		;

informat 	
VS 12. DOTT $15. SUMAEU best12.	
DOPL_1 best12.
;

format VS best12.;	
format DOTT	$15.;
format SUMAEU best12.;	
format DOPL_1 best12.;	

input 

VS 
DOTT $	
SUMAEU	
DOPL_1;

if _infile_ =: "VS" then delete;
run;

And it works, so I dont think there is problem with invalid data.

[yabwon]

Hi,

is the delimiter a comma or a semicolon, because in the first code you have `dlm="," ` and in the second `dlm=";" `, maybe this is the reason?

By the way, you don't have to create f1,..,f6, and f7.

All the bets

Bart

[Tesera]

Thank you very much! 

I didnt notice delimiter was different from previous code.

It works now!

Thank you very much for your time 🙂 I really appreciate it.

[Tesera · Accepted solution]

If anybody is looking for same answer as me, this was the solution to my problem 🙂

data 
%macro smallLoop1(s,e);
	%do year = &s. %to &e.;
	want&year.
	%end;
%mend smallLoop1;
%smallloop1(2008,2014)
wantOther
;

  length F $ 200;
  infile "\\PC0525\SAS_EG\IACS\*.csv" dlm=";" filename=F EOV=EOV missover Firstobs=2 	DSD
		LRECL=32767;
  input @;

if not EOV then do;

informat 
VS 12. 
DOTT $15. 
SUMAEU best12.	
DOPL_1 best12. 
;

format VS best12.;	
format DOTT	$15.;
format SUMAEU best12.;	
format DOPL_1 best12.;	

input 
VS 
DOTT $	
SUMAEU	
DOPL_1
;

select(compress(scan(F,-1,"\/"),,"KD"));
%macro smallLoop2(s,e);
%do year = &s. %to &e.;
when ("&year.") output want&year.;
%end;
%mend smallLoop2;
%smallLoop2(2008,2014)
otherwise output wantOther;
end;
end;
EOV = 0;
run;

[yabwon]

You may accept the the answer which helped you as a "solution" to have the thread closed as solved.
Bart

[Tom]

A coding style hint.  Do not bury the definition of a macro into the middle of a SAS step.  At a minimum define them between step boundaries, if not at the absolute top of the program.

%macro smallLoop1(s,e);
	%do year = &s. %to &e.;
	want&year.
	%end;
%mend smallLoop1;
%macro smallLoop2(s,e);
%do year = &s. %to &e.;
when ("&year.") output want&year.;
%end;
%mend smallLoop2;

data ...

[Tesera]

the first suggestion with 

if _infile_ =: 'VAR1' then delete;

worked for me very well, thank you!