topic Re: automate the process to import multiple spreadsheeet using proc import in SAS Programming

automate the process to import multiple spreadsheeet using proc import

Dd07 — Fri, 04 Mar 2016 14:44:19 GMT

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;

Re: automate the process to import multiple spreadsheeet using proc import

BrunoMueller — Fri, 04 Mar 2016 15:01:30 GMT

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

Re: automate the process to import multiple spreadsheeet using proc import

RW9 — Fri, 04 Mar 2016 15:03:32 GMT

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.

Re: automate the process to import multiple spreadsheeet using proc import

Dd07 — Fri, 04 Mar 2016 15:32:13 GMT

thankyou for writing..
could you just summarize or send me any reference for the data step which i can use for the automation ..

It will be great help

Re: automate the process to import multiple spreadsheeet using proc import

RW9 — Fri, 04 Mar 2016 17:11:51 GMT

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;

Re: automate the process to import multiple spreadsheeet using proc import

Peter_C — Sat, 05 Mar 2016 09:45:22 GMT

@RW9
Why do so many people add that trailing $ on INPUT statements when the data types are already defined as character?

Re: automate the process to import multiple spreadsheeet using proc import

Cynthia_sas — Sat, 05 Mar 2016 16:30:16 GMT

Hi, Peter:
I know it is not necessary, since the LENGTH statement establishes the column as CHAR, but I do it in the interests of maintenance. I used to work in a small shop where my only backup was a non-SAS person who understood that $ meant character, but did not necessarily know much else. So if he copied anything from my program, I wanted to be as explicit as possible. The $ in the LENGTH and $ in the INPUT were redundant, but I felt better leaving him with my programs that way.

cynthia

Re: automate the process to import multiple spreadsheeet using proc import

RW9 — Sat, 05 Mar 2016 17:25:43 GMT

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.