topic Re: How to import multiple datasets using macro in SAS Programming

How to import multiple datasets using macro

m1986MM — Thu, 20 Nov 2014 20:53:14 GMT

Hello everyone,

I have 180 delimited datasets and want to import them. These files are named monthly. For example: Stockprice_1998_01, Stockprice_1999_02, ... , Stockprice_2014_06.

My question is that how I can write a macro statement or even a do loop to import all the datasets and not write the proc import statement for each dataset.

Any suggestion would be greatly appreciated.

Thanks

Re: How to import multiple datasets using macro

Reeza — Thu, 20 Nov 2014 20:58:22 GMT

If all the files have the same structure then use a wild card statement:

data try01;

length filename txt_file_name $256;

retain txt_file_name;

infile "Path\*.txt" eov=eov filename=filename truncover;

input@;

if _n_ eq 1 or eov then do;

txt_file_name = scan(filename, -2, ".\");

eov=0;

end;

input

 *Place input code here;

;



run;

Re: How to import multiple datasets using macro

m1986MM — Fri, 21 Nov 2014 00:17:07 GMT

Thanks Reeza. Yes, my datasets have exactly same variables with the sam length and same format. But I didn't understand how I can write the import statement once and run it for all the datasets, named monthly. Would you please explain it more?

Thanks,

Re: How to import multiple datasets using macroReeza — Fri, 21 Nov 2014 01:25:41 GMT
Change the area's in red to match your directory and files structure.
data try01;
length filename txt_file_name $256;
retain txt_file_name;
*import all files in the directory path that has the name month_*.txt where the * is wildcard operator to imply any file starting with month_ and ends in .txt;
infile "Path\monthly_*.txt" eov=eov filename=filename truncover;
input@;
*This piece of code is used to extract the file name so you know where each record comes from;
if _n_ eq 1 or eov then do;
txt_file_name = scan(filename, -2, ".\");
eov=0;
end;
*Input statement here is used to input the dataset, which must be the same across all files;
input
*Place input code here;
;
run;

Re: How to import multiple datasets using macro

Babloo — Fri, 21 Nov 2014 06:53:25 GMT

please explain your scan function as I couldn't understand the usage of ".\"

txt_file_name = scan(filename, -2, ".\");

Re: How to import multiple datasets using macro

jakarman — Fri, 21 Nov 2014 07:40:28 GMT

us a filename to your directory with all those files.

filename infils "&path" ;

Then use:

data ..... ;

length filename $256 ;

infile infils("monthly_*.txt") filename=filename ... ; .

SAS(R) 9.4 Statements: Reference, Third Edition (infile stament) . When you want to have the membername use the last word of that long long name separated by the folder separation that is \ at windows / at Unix. As babloo is running at Unix, Reeza at Windows turn that slash.

Re: How to import multiple datasets using macro

Reeza — Fri, 21 Nov 2014 17:13:02 GMT

RTM - Scan function scans the file path for only the file name, using the delimiters of period and slash. If you're using unix as Jaap indicates change the sign of the slash.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Re: How to import multiple datasets using macro

m1986MM — Sat, 22 Nov 2014 04:20:11 GMT

Thanks.

Re: How to import multiple datasets using macro

venkatnaveen — Mon, 24 Nov 2014 08:16:46 GMT

Can u send the code to my mail id:

venkatnaveenmnba@gmail.com

im not getting explanation

Re: How to import multiple datasets using macro

venkatnaveen — Mon, 24 Nov 2014 08:34:22 GMT

If its in Enterprise guide 4.3 or DI Studio 4.21,

how to import?

Regards,

Naveen

Re: How to import multiple datasets using macro

m1986MM — Tue, 25 Nov 2014 02:20:20 GMT

Hi Naveen,

Which code do you mean?

Re: How to import multiple datasets using macro

venkatnaveen — Tue, 25 Nov 2014 06:01:52 GMT

SASCode for importing files

Re: How to import multiple datasets using macro

Reeza — Tue, 25 Nov 2014 07:22:48 GMT

You should ask your own question rather than post here as your question is not related.

The code for importing was provided above.

Please post your own question and expand on what you need in a single discussion

Enterprise Guide and SAS DI have different purposes and ways of importing. Are you importing text files? excel files? Are there multiple files or a single file? What exactly is the issue?

In EG You can use the Import Task, why not use that?

Re: How to import multiple datasets using macro

venkatnaveen — Tue, 25 Nov 2014 08:10:58 GMT

Thanks.