03-04-2016 09:44 AM
i want to automate to import multiple spreadhsheet using array.
Below is my code.
input dset $7. Src $30. sfil $42.;
Heatmap Heat Map_Nulls_Financia_Freq Load 9 Test 1 Q412_24Feb2016_rerun_dd.xlsx
p_value Sheet2 final data_deepak_2.xlsx
load9 Q4 load9.xlsx
ticket Tickets Tickets.xlsx
select dset into : sdata separated by ' ' from file_data;
select src into : sfile separated by ' ' from file_data;
select sfil into : spath separated by ' ' from file_data;
%put &var. &mnth. &cnt. &s_mth. &sdata. &sfile. &spath.;
Array dd(*) &sdata.;
array cc(*) &sfile.;
Array bb(*) &spath.;
do i=1 to dim(dd);
PROC IMPORT OUT= dd.dd(i)
03-04-2016 10:01 AM
Have a look at these two blog entries
by @ChrisHemedinger they explain in detail how to do what you want. It is all about running specific SAS code for a list of values.
The program pattern is using the SAS macro language and not the DATA Step.
03-04-2016 10:03 AM
This type of question gets asked a fair bit, there are several topics on here if you search. Firstly why do you have multiple files and why use Excel? If your going for an automated import process then I would suggest you step back from the how, and look at the whole picture. Where does the data come from, setup up an agreement to provide the data in an appropriate format (CSV, XML) at given times with a set format. Once that is signed off by all parties, then look at how best to get that data into your system. Any attempt to "just import a spreadsheet" will fall over every time it is run, I can pretty much guarentee it. Excel is by its nature not structured - what happens if the user puts a picture in cell one, or moves all the data to D:F? This is why it is far more important to get an agreement in place, i.e. this is wht you will send, this is how it will, deviation from this format will create overhead in terms of work.
Proc import is not the best tool for getting data in either, it is a guessing procedure. It looks at the file and guesses what it is supposed to do, this again is a place where the process can break down.
Once you have the agreement, the data is in an appropriate format, then the whole process of automating it is very simple, can be done with either 1 or two datasteps and will be pretty robust, plus if it fails, its most likely to be the vendor not complying with the agreement.
03-04-2016 10:32 AM
03-04-2016 12:11 PM
Well,the datastep is really quite basic. Its more about the process, and the data. Do you have one CSV to import, if there are multiple CSV files, can they be appened (i.e. are they thw same). The code is simple - the documentation is the difficult bit:
data want; infile "c:\<your_file>.csv"; length var1 $20 var3 $10...; input var1 $ var2 var3 $...; format ...; run;
03-05-2016 04:45 AM
03-05-2016 11:30 AM
03-05-2016 12:25 PM
Habit is part of it. Also, if it doesn't matter if it is there or not, but it makes reading the input line easliy then I would always put it in. Much the same as with cat() function, you don't need to explicitly put numbers to character, however i would always do that anyway to make it very clear what is happening. Actual programming is very low on the priorty to me, documentation and easy to read code is far more important that saving a few characters.