BookmarkSubscribeRSS Feed
Dd07
Fluorite | Level 6

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;

7 REPLIES 7
BrunoMueller
SAS Super FREQ

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Dd07
Fluorite | Level 6
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Peter_C
Rhodochrosite | Level 12
@RW9
Why do so many people add that trailing $ on INPUT statements when the data types are already defined as character?
Cynthia_sas
SAS Super FREQ
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1047 views
  • 2 likes
  • 5 in conversation