Hello All,
i want to automate to import multiple spreadhsheet using array.
please suggest.
Below is my code.
data file_data;
input dset $7. Src $30. sfil $42.;
cards;
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
;
run;
====================
proc sql;
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;
quit;
%put &var. &mnth. &cnt. &s_mth. &sdata. &sfile. &spath.;
%let path=C:\Users\212475280\Desktop\RDG\;
%put &path.;
=======================
Data _Null_;
Array dd(*) &sdata.;
array cc(*) &sfile.;
Array bb(*) &spath.;
do i=1 to dim(dd);
PROC IMPORT OUT= dd.dd(i)
DATAFILE= "&path.bb(i)"
DBMS=EXCEL REPLACE;
RANGE="cc(i)$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
end;
run;
Hi
Have a look at these two blog entries
http://blogs.sas.com/content/sasdummy/2012/03/20/sas-program-by-processing/
http://blogs.sas.com/content/sasdummy/2012/03/23/improving-on-a-sas-programming-pattern/
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.
Bruno
Hi,
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.