topic Re: Importing multiple files and merging into one database in SAS Programming

Importing multiple files and merging into one database

deleted_user — Thu, 05 Mar 2009 16:55:05 GMT

Hi,

I have given the task of importing many files merging them then combining these into a large database. Here is the setup.

I have 7 files from certain days and certain locations throughout a year in a folder titled with the respective year (2004-2008). Something like this: C:\Users\mherrin\Documents\Data\2005
Each of these folders contains 20,000+ files

I would like to import 3 of the 7 files from each day and location and merge them so I have one file per day per location. Then repeat this process for each day and location for all the files in the folder.

The next step will be to combine all of these into one data file for the entire year. Repeat this process in each folder then combine the yearly data files into a comprehensive database.

I have written the code to import and merge the files but now I think I need to make a macro to repeat this process.

Any help would be greatly appreciated. THANKS

Re: Importing multiple files and merging into one database

sbb — Thu, 05 Mar 2009 17:35:40 GMT

The SAS Macro Language Guide has a section on the topic "Generating Repetitive Pieces of Text Using %DO Loops", which discusses using %DO/%END loops within a macro execution.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/documentation/onlinedoc/91pdf/sasdoc_91/base_macro_6997.pdf

Re: Importing multiple files and merging into one database

deleted_user — Thu, 05 Mar 2009 22:46:27 GMT

I am using the pipe command to read all of the file names out of my folder like so:

filename indata pipe 'dir C:\Users\mherrin\Documents\trial /b';
data file_list;
length fname $20;
infile indata truncover; /* infile statement for file names */
input fname $20.; /* read the file names from the directory */
call symput ('fname',fname);
call symput ('num_files',_n_); /* store the record number in a macro variable */
call symput ('name',substr(fname,1,11));
run;

But then how do I get my proc import command to read all of those file names?
This is what I have tried.

%macro work;
PROC IMPORT OUT= &fname
DATAFILE= "C:\Users\mherrin\Documents\trial\&fname"
DBMS=CSV REPLACE;
getnames=no;
RUN;
%mend work;

I am obviously missing the loop part of this process. I am sure this is something simple that I just cannot find. If I could get these to read in I think I would be on my way to figuring out the rest. Thanks!

Re: Importing multiple files and merging into one database

data_null__ — Thu, 05 Mar 2009 23:03:57 GMT

I think reading 20,000+ files with PROC IMPORT will be too slow.

1) Can the files be read using the same input statements. Hopefully you don't have 20,000 file with vastly different record layout.

2) show directory and file name "structure" in more detail. You description was too abstract for me, I'm not very bright.

3) I didn't get that bit about 3 of 7 or something and how that is determined.

4) This call probably all be done with ONE data step. But I need to know more details. The "have" and "need" so to speak.

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 00:42:30 GMT

Sure I will explain more, anything for some help.

In each data folder there are 20,000+ files. 7 from each day and each location, these have different input layouts. Currently I need to merge only 3 of these 7 daily files. The paths and filenames are:

C:\Users\mherrin\Documents\trial\APX05132005.1
C:\Users\mherrin\Documents\trial\APX05132005.2
C:\Users\mherrin\Documents\trial\APX0513.DRF

for another day it would be like:

C:\Users\mherrin\Documents\trial\APX05142005.1
C:\Users\mherrin\Documents\trial\APX05142005.2
C:\Users\mherrin\Documents\trial\APX0514.DRF

Where 05 is the month 14 is the day 2005 is the year.
And for another location:

C:\Users\mherrin\Documents\trial\PEN05142005.1
C:\Users\mherrin\Documents\trial\PEN05142005.2
C:\Users\mherrin\Documents\trial\PEN0514.DRF

I need to import each of these sets of three and merge them into a daily file.

The files come without variable names, so I inefficiently named them with a data step using the rename function. But that part is done.

I have written code to input one specific file for all 7 types and the code to merge the 3 desired together.

We can focus on just the 3 desired files for now, so it will be more like 10,000 files per year. We bought a machine that is supposed to be FAST (dell precision T7400). I think what I need is a macro that will cycle through these files importing them into sas and merging them into a daily file. These will then be appended into one yearly data set.

I hope you can help. Thanks.

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 10:21:26 GMT

Hi,

Your input datastep from the pipe command will only ever give you the last line it processed, ie the last file. You need to give the macro variables you create some sort of unique identifier for each file. Just an iterative number will do and it might as well be the _n_ of the record being processed.

So when you do the call symput write (sorry I cant see the original post but it was something like this):

call symput(compress('fname'!!_n_),filename);

You will also need to store the total number of files so put a call symput in for that too (it will execute every time but when it's done it will have the value of the last one, you could make this more efficient if you wanted)....

call symput('tot_files',_n_);

Then you will end up with a macro variable for each file..

fname1
fname2
fname3 etc

then create a loop in the macro to run through each fname and import. Something like (I hope you understand double ampersands, if not see documentation)...

%do i %to &tot_files;
proc import out=&&fname&i datafile='..........
..........
%end;

Hopefully that points you in the right direction. Obviously the %d loop will need to be inside a macro.

Message was edited by: pznew Message was edited by: pznew

Re: Importing multiple files and merging into one database

data_null__ — Fri, 06 Mar 2009 12:19:26 GMT

I'm still not sure exactly where the similarities are I will assume all file types have the same record structure. Therefore all type 1 files can be read with one data step. This example creates data(files) and reads them, into one data set.

Let me know your questions. All infile and file options are documented. And PIPE example are found in "Companion" and at support.sas.com

[pre]
dm 'clear log; clear output;';
* create test data(files to be read in next step);
data _null_;
length command $256;
infile cards truncover;
input name :$128.;
if _n_ eq 1 then do;
path = quote(substr(name,1,find(name,'\',-length(name))));
command = 'rmdir /S /Q ".\xUsers\"';
infile dummy1 pipe filevar=command;
command = catx(' ','mkdir',path);
put command=;
infile dummy1 pipe filevar=command;
rc = sleep(1); *small delay;
end;
file dummy filevar=name dsd dlm=',';
do _n_ = 1 to 10;
put _n_:z5. name;
end;
cards;
.\xUsers\mherrin\Documents\trial\APX05132005.1
.\xUsers\mherrin\Documents\trial\APX05132005.2
.\xUsers\mherrin\Documents\trial\APX0513.DRF
.\xUsers\mherrin\Documents\trial\APX05142005.1
.\xUsers\mherrin\Documents\trial\APX05142005.2
.\xUsers\mherrin\Documents\trial\APX0514.DRF
.\xUsers\mherrin\Documents\trial\PEN05142005.1
.\xUsers\mherrin\Documents\trial\PEN05142005.2
.\xUsers\mherrin\Documents\trial\PEN0514.DRF
;;;;
run;

*** Read all the type .1 records;
data Type1;
length command filename fname path $256.;
command = 'dir /s /b ".\xUsers"';
infile dummy1 pipe filevar=command truncover;
input path $256.;
if scan(path,-1,'.') eq '1';
put _infile_;
infile dummy2 filevar=path filename=fname end=eof dsd;
filename = scan(fname,-2,'.\');
length prefix $3;
prefix = filename; * assumes prefix is always first 3 chars;
date = input(substr(filename,anydigit(filename)),mmddyy10.);
do while(not eof);
input obs name:$64.; ** Fields for type 1 files?;
output;
end;
format date date9.;
run;
proc print;
run;
[/pre]

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 17:04:43 GMT

Thanks for the help.

I am having some success with the do loop in a macro I think but I get two errors;

1) ERROR: Library name is not assigned.
2)ERROR: Libname 'APX05132005' exceeds 8 characters.

I think this is due to the fact that &&fname&i converts to APX05132005.1 and sas thinks that the first part is the libname.

I would like to use an if statement as well in the macro. This is what I am thinking of doing. I commented it so you would have a better chance of understanding what I am "trying" to do. Please point out any mistakes. I appreciate all the help.

filename indata pipe 'dir C:\Users\mherrin\Documents\trial /b';
data file_list;
length fname $20;
infile indata truncover; /* infile statement for file names */
input fname $20.; /* read the file names from the directory */
call symput (compress('fname'!!_n_),fname);
call symput ('num_files',_n_); /* store the record number in a macro variable */
call symput (compress('name'!!_n_),substr(fname,1,11));
run;

%put _global_;

%macro work;
%do i=1 %to &num_files;
PROC IMPORT OUT= '&&fname&i'
DATAFILE= "C:\Users\mherrin\Documents\trial\&&fname&i"
DBMS=CSV REPLACE;
getnames=no;
RUN;
%if scan(&&fname&i,-1,'.') eq '1' %then %do; * for the .1 files;
data &&fname&i;
set &&fname&i(rename=(var1=... var99=);
run;
data &&name&i; *This will be the comprehensive daily file;
set &fname&i; *(i.e. APX05132005 for the first set of files);
run;;
%end;
%else %if scan(&&fname&i,-1,'.') eq '2' %then %do; /*for the .2 files*/
data &&fname&i;
set &&fname&i(rename=(var1=... var99=); /*different variable names*/
run;
data &&name&i; /*Merge the first two files together to begin the comprehensive file*/
merge &&name&i &&fname&i;
by raceNum;
if PostPosition=99 then delete; /*can you do this in the same data step?*/
run;
data &&name&i;
set &&name&i(drop=PostPos PostPosition); /*these must be removed for merge later*/
run;
%end;
%else %if scan(&&fname&i,-1,'.') eq 'D' %then %do; /*for the .drf files*/
data &&fname&i;
set &&fname&i(rename=(var1=... var1435=); /*different variable names*/
run;
data &&name&i-; /*can you do an i-1 type macro? Because the way I used name &&name&i would be APX0513.DRF in this case*/
merge &&name&i &&fname&i;
by raceNum ProgNum; /*I may have to sort these sets first*/
run;
%end;
%mend work;

%work;

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 17:16:36 GMT

It will certainly think the . is a seperator between the libref and the dataset name. You should change the dot to something else. Use the translate function to do that. Also you need to surround the macro variable with double quites " and not single ' or it will not resolve.

Do somethig like this....

out="%sysfuc(translate(&&fname&i, _ , . ))";

the sysfunc tells the macro language to use the sas function (remember the macro language and sas language are different things). And because we are not dealing with quoted strings in macro but just characters we do not put quotes round the characters on the translate function that we are translating to and from. ie to _ from .

Hoipe that helps in some way.

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 19:21:02 GMT

Thanks that was a great help. One funny thing is happening though. When I run the code the APX05132005_1 sas data file is not correct. It contains a list of path names.

I tried running this with if statements as well but I received an error reading the following:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand
is required. The condition was: scan(&&fname&i,-1,'.') eq '1'

I feel like we are getting closer. Thank you for helping.

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 19:41:22 GMT

Ok both of those errors are fixed.

Now I just cant read in the .drf files.

%if %scan(&&fname&i,-1,'.')='d' %then %do;

Is not working. Thanks Message was edited by: Bryce

Re: Importing multiple files and merging into one database

deleted_user — Fri, 06 Mar 2009 23:08:15 GMT

I think I have it running now. Thanks, you all have been a great help.

One more question for now:
Is there anyway to prevent the window full popup from occuring. maybe set it to a clear all everytime?

Or should I add a clear window function inside the loop?

I think it will take 8 hours to import. :S I would "prefer" not to sit here clicking the clear button. I closed the log window now the message just sais "window full" not "log window full"

I would greatly appreciate it if anyone could answer tonight so I could get all this data imported over the weekend!

Re: Importing multiple files and merging into one database

data_null__ — Fri, 06 Mar 2009 23:25:53 GMT

Run it in batch. Right click filename from winders explorer and select "Batch Submit with SAS 9.1" Good luck with 20,000 proc imports and all those other steps, I don't think it will ever finish.

Re: Importing multiple files and merging into one database

deleted_user — Sun, 08 Mar 2009 16:03:56 GMT

Yeah, your right. It has been running over the weekend and it is only the the H's

what can I do differently? maybe I could import and combine all the same files then change the variable names instead of renaming them in each iteration? Or should I stay away from proc import all together?

Originally it was running faster about 8 seconds per loop so it should have taken 8 hours. It is running much slower now 15 seconds per loop. why is this?

Let me know if there is a more efficient way of doing this. Thank you!

Re: Importing multiple files and merging into one database

sbb — Sun, 08 Mar 2009 18:04:07 GMT

Ensure that you are current on SAS HOTFIX maintenance, as a general rule. Also, it may help with SAS WORK processing, if you can relocate your WORK allocation to another physical drive. There are technical papers on the SAS support website http://support.sas.com/ to address the SAS Windows performance topic - here is one for consideration (maybe too much detail, but useful info about SAS and I/O such as WORK):

http://support.sas.com/techsup/technote/ts684/ts684.html

Scott Barry
SBBWorks, Inc.

Re: Importing multiple files and merging into one database

data_null__ — Mon, 09 Mar 2009 14:38:08 GMT

> Yeah, your right. It has been running over the
> weekend and it is only the the H's
>
> what can I do differently? maybe I could import and
> combine all the same files then change the variable
> names instead of renaming them in each iteration? Or
> should I stay away from proc import all together?
Yes, forget PROC IMPORT, you mentioned the data had no field names row so you are better off writing your own data step to read the data. Plus you have many file that are similarly structured.

I would just write a data step to read all the files of a particular type. I gave you a example but it may not have been adequate. You did not provide enough details about the fields to do any more than demonstrate one method of read multiple files.

>
> Originally it was running faster about 8 seconds per
> loop so it should have taken 8 hours. It is running
> much slower now 15 seconds per loop. why is this?

It is hard to guess as but leaky memory seems reasonable. Or maybe your PC just got tired. 🙂

>
> Let me know if there is a more efficient way of doing
> this. Thank you!
data step.... details to be determined.

Re: Importing multiple files and merging into one database

deleted_user — Mon, 09 Mar 2009 14:56:44 GMT

I looked over your example but I did not follow it as well so I tried to stick with the import code. I will work on that version now. I do not know how to make the 'cards' option work for the number of files I have. maybe if i run it through a macro and put the &&filename&i at the end of the path.

If you want an idea of exactly what I am trying to do my code is posted below. It preforms the task but not quickly enough.

You can see that I am making one file per day per location. Would it be more efficient if I began making a yearly data set and delete each daily file after I append it to the end of the yearly data set? That way sas would not have as many data files in the _2005_ library.

I will try to work my way through your code now. Thanks!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

libname _2005_ "C:\Users\mherrin\Documents\Horse_Racing\daily2005";

filename indata pipe 'dir C:\Users\mherrin\Documents\Horse_Racing\2005 /b';
data file_list;
length fname $20;
infile indata truncover; /* infile statement for file names */
input fname $20.; /* read the file names from the directory */
call symput (compress('fname'!!_n_),fname);
call symput ('num_files',_n_); /* store the record number in a macro variable */
call symput (compress('name'!!_n_),substr(fname,1,11));
run;

%put _global_;

%macro import;
%do i=1 %to &num_files;
dm 'clear log; clear output;';
%if %scan(&&fname&i,-1,'.')=1 %then %do;
PROC IMPORT OUT= %sysfunc(translate(&&fname&i,_,.))
DATAFILE= "C:\Users\mherrin\Documents\Horse_Racing\2005\&&fname&i"
DBMS=CSV REPLACE;
getnames=no;
RUN;
data %sysfunc(translate(&&fname&i,_,.));
set %sysfunc(translate(&&fname&i,_,.))(rename=(var1=Track var2=Date var3=RaceNum var4=DayFlag var5=Dist
var6=DistUnit var7=DistFlag var8=Surf1 var9=Surf2
var11=AllWeather var12=Chute var13=BrisType var14=EQBType var15=Grade
var16=ASRest var17=RRest var18=StateFlag var19=Class var20=Breed
var21=Country var22=Purse var23=TotValue
var28=MaxClmgPrice var30-var34=RC1-RC5
var37=FieldSize var38=TrackCond var39-var43=Frac1-Frac5 var44=FinalTime
var45-var49=FracDist1-FracDist5
var50=OffTime var51=StartCallDist var52-var54=CallDist1-CallDist3 var55=RaceName
var56=StartDesc var57=RailDist var58= OffTurf var59=OTDistChange
var63=Weather var64=RaceTemp var65=WPSPool)
drop=var10 var24-var27 var29 var35 var36 var60-var62 var66-var99);
run;
data %substr(%sysfunc(translate(&&fname&i,_,.)),1,11);
merge %sysfunc(translate(&&fname&i,_,.)) %substr(%sysfunc(translate(&&fname&i,_,.)),1,7);
by RaceNum;
run;
%end;
%if %scan(&&fname&i,-1,'.')=2 %then %do;
PROC IMPORT OUT= %sysfunc(translate(&&fname&i,_,.))
DATAFILE= "C:\Users\mherrin\Documents\Horse_Racing\2005\&&fname&i"
DBMS=CSV REPLACE;
getnames=no;
RUN;
data %sysfunc(translate(&&fname&i,_,.));
set %sysfunc(translate(&&fname&i,_,.))(rename=(var1=Track var2=Date var3=RaceNum var4=DayFlag var5=HorseName
var6=ForeignBred var7=StateBred var8=PostPosition var9=ProgNum
var10=YoB var11=Breed var12=CoupledFlag var13=AbbJName
var14=JLast var15=JFirst var16=JMiddle
Var18=AbbTName var19=TLast var20=TFirst var21=TMiddle
var22=TripComment var24=OName var25=OFirst var26=OMiddle
var27=ClmgPrice var28=MedCode var29=EquipCode Var30=Earnings
var31=Odds var32=NonBetFlag var33=FavFlag var36=DQFlag
var37=DQPlacing var38=Weight var39=CorWeight var40=OWAmount
var41=Claimed var42=ClmAbbTrn var43=ClmTrnLast var44=ClmTrnFirst
var45=ClmTrnMiddle var47=ClmAbbOwn var48=ClmOwnLast
var49=ClmOwnFirst var50=ClmOwnMiddle var51=WinPO var52=PlacePO
var53=ShowPO var55=StPos var56-var58=CallPos1-CallPos3
var59=StretchPos var60=FinishPos Var61=OfficialPos
var62=StLengthsAhd var63-var65=CallLengthsAhd1-CallLengthsAhd3
var66=StretchLengthsAhd var67=FinishLengthsAhd var68=StLengthsBhd
var69-var71=CallLengthsBhd1-CallLengthsbhd3 var72=StrechLengthsBhd
var73=FinishLengthsBhd var74=StCallMgn var75-var77=CallMgn1-CallMgn3
var78=StretchMgn var79=FinishMgn var80=DeadHeatFlag)
drop=var17 var23 var34 var35 var46 var54 var81-var99);
run;
data %sysfunc(translate(&&fname&i,_,.));
set %sysfunc(translate(&&fname&i,_,.));
if PostPosition=99 then delete;
run;
data %substr(%sysfunc(translate(&&fname&i,_,.)),1,11);
set %substr(%sysfunc(translate(&&fname&i,_,.)),1,11)(drop=PostPosition PostPos);
run;
proc sort data=%substr(%sysfunc(translate(&&fname&i,_,.)),1,11);
by racenum prognum;
run;
proc sort data=%sysfunc(translate(&&fname&i,_,.));
by racenum prognum;
run;
data %substr(%sysfunc(translate(&&fname&i,_,.)),1,11);
merge %substr(%sysfunc(translate(&&fname&i,_,.)),1,11) %sysfunc(translate(&&fname&i,_,.));
by racenum prognum;
run;
data _2005_.%substr(%sysfunc(translate(&&fname&i,_,.)),1,11);
set %substr(%sysfunc(translate(&&fname&i,_,.)),1,11);
if postposition=. then delete;
run;
PROC DATASETS LIB=WORK MEMTYPE=DATA
kill;
RUN;

%end;
%if %scan(&&fname&i,-1,'.')=DRF %then %do;
PROC IMPORT OUT= %sysfunc(translate(&&fname&i,_,.))
DATAFILE= "C:\Users\mherrin\Documents\Horse_Racing\2005\&&fname&i"
DBMS=CSV REPLACE;
getnames=no;
RUN;
data %sysfunc(translate(&&fname&i,_,.));
set %sysfunc(translate(&&fname&i,_,.))(rename=(var1=Track var2=Date var3=RaceNum var4=PostPos var5=Entry
var6=Dist var7=Surface var9=RaceType var10=ASRest
var11=RaceClass var12=Purse var13=ClmgPrice var14=ClmgPriceHorse var15=TrackRecord
var16=RaceCond var17=LasixList var18=ButeList var19=CoupledList var20=MutuelList
var21=SimHT var22=SimHTRaceNum var23=BreedType var24=NasalStpChng var25=AllWeather
var28=Trn var29=TrnStarts var30=TrnWins var31=TrnPlaces var32=TrnShows
var33=Jockey var34=AppWgtAllow var35=JStarts var36=JWins var37=Jplaces var38=JShows
var39=Owner var40=OSilks var41=MainTrackOnly var43=ProgNum var44=MornLine
var45=HorseName var46=Yob var47=FoalMonth var49=Sex Var50=HColor
var51=Weight var52=Sire var53=SSire var54=Dam var55=DSire
var56=Breeder var57=StCountryBred var58=PostPosition
var62=NewMeds var63=OldMeds var64=EquipChng
var65=TdyDistStarts var66=TdyDistWins var67=TdyDistPlaces var68=TdyDistShows var69=TdyDistEarnings
var70=TdyTrackStarts var71=TdyTrackWins var72=TdyTrackPlaces var73=TdyTrackShows var74=TdyTrackEarnings
var75=TurfStarts var76=TurfWins var77=TurfPlaces var78=TurfShows var79=TurfEarnings
var80=WetStarts var81=WetWins var82=WetPlaces var83=WetShows var84=WetEarnings
var85=CurYear var86=CurYearStarts var87=CurYearWins var88=CurYearPlaces var89=CurYearShows var90=CurYearEarnings
var91=PrevYear var92=PrevYearStarts var93=PrevYearWins var94=PrevYearPlaces var95=PrevYearShows var96=PrevYearEarnings
var97=LifeStarts var98=LifeWins var99=LifePlaces var100=LifeShows var101=LifeEarnings

var102-var113=WODate1-WODate12
var114-var125=WOTime1-WOTime12
var126-var137=WOTrack1-WOTrack12
var138-var149=WODist1-WODist12
var150-var161=WOTrackCond1-WOTrackCond12
var162-var173=WODescript1-WODescript12
var174-var185=MITrackIndicator1-MITrackIndicator12
var186-var197=NumWorksDD1-NumWorksDD12
var198-var209=RankDD1-RankDD12

var210=RunStyle var211=QuirinSpdPts var214=f2BrisPacePar var215=f4BrisPacePar var216=f6BrisPacePar
var217=BrisSpeedParClass var218=BrisLatePacePar var224=DaysSinceLR var225-var230=DRFRaceCond1-DRFRaceCond6
var231=AWSLifeStarts var232=AWSLifeWins var233=AWSLifePlaces var234=AWSLifeShows var235=AWSLifeEarnings
var236=AWSBestBrisSpeed var238=LowClmgPrice var239=StateBredFlag
var240-var248=WagerType1-WagerType9
var251=BrisPowerRtng

var256-var265=RaceDate1-RaceDate10
var266-var274=DaysSinceLR1-DaysSinceLR9
var276-var285=Track1-Track10
Var286-var295=BrisTrack1-BrisTrack10
Var296-var305=RaceNum1-RaceNum10
var306-var315=TrackCond1-TrackCond10
var316-var325=Dist1-Dist10
var326-var335=Surf1-Surf10
var336-var345=SpecChute1-SpecChute10
Var346-var355=NumEntrants1-NumEntrants10
var356-var365=PostPosition1-PostPosition10
var366-var375=Equip1-Equip10
Var386-var395=Med1-Med10
Var396-var405=TripComment1-TripComment10
var406-var415=WinName1-WinName10
var416-var425=SecndName1-SecndName10
var426-var435=ThrdName1-ThrdName10
var436-var445=WinWgt1-WinWgt10
var446-var455=SecndWgt1-SecndWgt10
var456-var465=ThrdWgt1-ThrdWgt10
var466-var475=WinMgn1-WinMgn10
var476-var485=SecndMgn1-SecndMgn10
var486-var495=ThrdMgn1-ThrdMgn10
var496-var505=Comment1-Comment10
var506-var515=Wgt1-Wgt10
var516-var525=Odds1-Odds10
var526-var535=Entry1-Entry10
var536-var545=RaceClass1-RaceClass10
var546-var555=ClmgPrice1-ClmgPrice10
var556-var565=Purse1-Purse10
var566-var575=StartCallPos1-StartCallPos10
var576-var585=FrstCallPos1-FrstCallPos10
var586-var595=SecndCallPos1-SecndCallPos10
var596-var605=GateCallPos1-GateCallPos10
var606-var615=StretchPos1-StretchPos10
var616-var625=FinishPos1-FinishPos10
var626-var635=MoneyPos1-MoneyPos10
var636-var645=StartCallLgnbtnLead1-StartCallLgnbtnLead10
var646-var655=StartCallLgnBtn1-StartCallLgnBtn10
var656-var665=FrstCallLgnbtnLead1-FrstCallLgnbtnLead10
var666-var675=FrstCallLgnbtn1-FrstCallLgnbtn10
var676-var685=SecndCallLgnbtnLead1-SecndCallLgnbtnLead10
var686-var695=SecndCallLgnbtn1-SecndCallLgnbtn10
var696-var705=Shape1stCall1-Shape1stCall10
var716-var725=StretchLgnBtnLead1-StretchLgnBtnLead10
var726-var735=StretchLgnBtn1-StretchLgnBtn10
var736-var745=FinishLgnBtnLead1-FinishLgnBtnLead10
var746-var755=FinishLgnBtn1-FinishLgnBtn10
var756-var765=RaceShape2ndCall1-RaceShape2ndCall10
var766-var775=f2Pace1-f2Pace10
var776-var785=f4Pace1-f4Pace10
var786-var795=f6Pace1-f6Pace10
var796-var805=f8Pace1-f8Pace10
var806-var815=f10Pace1-f10Pace10
var816-var825=LatePace1-LatePace10

var846-var855=BrisSpeedRating1-BrisSpeedRating10
var856-var865=DRFSpeedRating1-DRFSpeedRating10
var866-var875=DRFTrackWgt1-DRFTrackWgt10
var876-var885=f2Frac1-f2Frac10
var886-var895=f3Frac1-f3Frac10
var896-var905=f4Frac1-f4Frac10
var906-var915=f5Frac1-f5Frac10
var916-var925=f6Frac1-f6Frac10
var926-var935=f7Frac1-f7Frac10
var936-var945=f8Frac1-f8Frac10
var946-var955=f10Frac1-f10Frac10
var956-var965=f12Frac1-f12Frac10
var966-var975=f14Frac1-f14Frac10
var976-var985=f16Frac1-f16Frac10
var986-var995=Num1Frac1-Num1Frac10
var996-var1005=Num2Frac1-Num2Frac10
var1006-var1015=Num3Frac1-Num3Frac10
var1036-var1045=FinalTime1-FinalTime10
var1046-var1055=Claimed1-Claimed10
var1056-var1065=Trn1-Trn10
var1066-var1075=Jock1-Jock10
var1076-var1085=AppWgtAllow1-AppwgtAllow10
var1086-var1095=RaceType1-RaceType10
var1096-var1105=ASRest1-ASRest10
var1106-var1115=StateBred1-StateBred10
var1116-var1125=QualFlag1-QualFlag10
var1126-var1135=Favorite1-Favorite10
var1136-var1145=FBandage1-FBandage10

var1147=CurYearTrnStarts var1148=CurYearTrnWins var1149=CurYearTrnPlaces var1150=CurYearTrnShows
var1151=CurYearTrnROI var1152=PrevYearTrnStarts var1153=PrevYearTrnWins var1154=PrevYearTrnPlaces
var1155=PrevYearTrnShows var1156=PrevYearTrnROI var1157=CurYearJStarts var1158=CurYearJWins
var1159=CurYearJPlaces var1160=CurYearJShows var1161=CurYearJROI var1162=PrvYearJStarts
var1163=PrevYearJWins var1164=PrevYearJPlaces var1165=PrevYearJShows var1166=PrevYearJROI

var1167-var1176=BrisSpeedParClass1-BrisSpeedParClass10

var1177=CurrSireStudFee
var1178=BestSpFast
var1179=BestSPTurf
var1180=BestSpOffTrack
var1181=BestSpDist
var1182-var1191=BarShoe1-BarShoe10
var1192-var1201=CompLine1-CompLine10
var1202-var1211=LowClmgPrice1-LowClmgPrice10
var1212-var1221=HighClmgPrice1-HighClmgPrice10
var1222=AuctionPrice
var1223=WhereWhenAuction
var1254-var1263=Nasaloff1-Nasaloff10
var1264=DirtPedRtng var1265=MudPedRtng var1266=TurfPedRtng var1267=DistPedRtng var1328=LifeBestSpeed
var1329=FrstRecYrBestSpeed var1330=SecndRecYrBestSpeed var1331=TodayTrackBestSpeed var1332=FDNumStarts var1333=FDNumWins
var1334=FDNumPlaces var1335=FDNumShows var1336=FDNumEarnings var1337=KeyStatNum1 var1338=StartsNum1
var1339=WinPercNum1 var1340=ITMPercNum1 var1341=twodolROINum1 var1342=KeyStatNum2 var1343=StartsNum2 var1344=WinPercNum2
var1345=ITMPercNum2 var1346=twodolROINum2 var1347=KeyStatNum3 var1348=StartsNum3 var1349=WinPercNum3
var1350=ITMPercNum3 var1351=twodolROINum3 var1352=KeyStatNum4 var1353=StartsNum4 var1354=WinPercNum4
var1355=ITMPercNum4 var1356=twodolROINum4 var1357=KeyStatNum5 var1358=StartsNum5 var1359=WinPercNum5
var1360=ITMPercNum5 var1361=twodolROINum5 var1362=KeyStatNum6 var1363=StartsNum6 var1364=WinPercNum6
var1365=ITMPercNum6 var1366=twodolROINum6 var1367=JDistJonTurfLabel var1368=JDistJonTurfstarts
var1369=JDistJonTurfWins var1370=JDistJonTurfPlaces var1371=JDistJonTurfShows var1372=JDistJonTurfROI
var1373=JDistJonTurfEarnings var1374=PostTimesRegion var1383-var1392=ExtStartComment1-ExtStartComment10
var1393-var1402=SealedTrack1-SealedTrack10 var1403-var1412=PrevAWSurfFlag1-PrevAWSurfFlag10)

drop=var8 var26 var27 var42 var48 var59-var61 var212 var213 var219-var223 var237 var249 var250 var252-var255 var275 var376-var385
var706-var715 var826-var845 var1016-var1035 var1146 var1224-var1253 var1268-var1327 var1375-var1382 var1413-var1435);
run;
data %substr(%sysfunc(translate(&&fname&i,_,.)),1,7);
set %sysfunc(translate(&&fname&i,_,.));
run;
%end;
%end;
%mend import;
%import; Message was edited by: Bryce

Re: Importing multiple files and merging into one database

data_null__ — Mon, 09 Mar 2009 15:25:14 GMT

One thing you want to change is the looping. You want to read all the .1 files in a single data step that is what my example attemtents to demonstrate. Then you can do similar for .2 and .DRF. When these three steps are one you will have 3 SAS data sets that you can summarize as you see fit.

I will work to make a more specifiy example. Can you make some of the files available online somehow?

Also if you can copy the PROC IMPORT generated data step code from a SAS log. This will be the same code we need to put in the data step we write that reads all the file of type .1

Re: Importing multiple files and merging into one database

deleted_user — Mon, 09 Mar 2009 15:26:53 GMT

Wow!!!

Where do I start with that?

Firstly you will need to read the files using the infile and input statements like this:

data yourdata;
infile xxxxxx dsd;
input variableA variableB etc etc

I cant explain how all that works here I would point you in the direction of the SAS documentation.

Secondly, use as few data steps as possible. Every time you put a set and data statment SAS has to read though every record in the data set which uses up resources. Do as much manipulation as you can in the one data step, ie the one you read the files in. SO do your deletes and renames and everything in there. DOing a data step simply to rename variables is a complete waste of resources and will slow you down.

Only keep the variables you are interested in. If, as I can see in one of them, you are not interested in the last 10 variables, just don't bother reading them in from the file (I appreciate that import will do this anyway, but when you use the infile and input statement, just read in what you need).

Also the amount of data in your _2005_ library will have no effect on how quickly it runs, you only need to worry about clearing that out if you are going to run out of space.

As I say, the imperative thing is to keep your datasteps to a minimum. If you think of datasets as books. Each datastep is like going through every page. If you were going to sign your name of every page in a book and put a date at the top you wouldn't run through every page signing your name, then go back to the start and put the date of every page, you'd be much faster putting both the date and signature on each page at the same time. Hope that analogy isn't too absract.

Re: Importing multiple files and merging into one database

deleted_user — Mon, 09 Mar 2009 15:28:37 GMT

I wanted to walk through this, I am commenting it so maybe you can direct me in the things I do not understand.

dm 'clear log; clear output;';
* create test data(files to be read in next step);
data _null_;
length command $256; *command will be the location of the files;
infile cards truncover; *tells SAS to read through the cards later in the program;
input name :$128.; *what it reads in will be called 'name' What does the ':' do?
if _n_ eq 1 then do;
path = quote(substr(name,1,find(name,'\',-length(name)))); *define the path name;
command = 'rmdir /S /Q ".\xUsers\"'; *don't understand this step;
infile dummy1 pipe filevar=command; *read all of the files located in the command location (or open a pipe to that location);
command=catx(' ','mkdir',path); *creates this directory with the location designated in path;
put command=;
infile dummy1 pipe filevar=command; *pipe again to command location. What exactly does filevar= do?;
rc = sleep(1);
end;
file dummy filevar=name dsd dlm=',';
do _n_ = 1 to 6;
put _n_:z5. name;
end;
cards; *read the cards. How do I get ALL the files I need here?;
.\xUsers\mherrin\Documents\trial\APX05132005.1
.\xUsers\mherrin\Documents\trial\APX05132005.2
.\xUsers\mherrin\Documents\trial\APX0513.DRF
.\xUsers\mherrin\Documents\trial\APX05142005.1
.\xUsers\mherrin\Documents\trial\APX05142005.2
.\xUsers\mherrin\Documents\trial\APX0514.DRF
;;;;
run;

*Read Type .1 (RACE) files;

data type1;
length command filename fname path $256.;
command = 'dir /s /b ".\xUsers"'; *dir has not been defined how does this work?;
infile dummy1 pipe filevar=command truncover;
input path $256.;
if scan(path,-1,'.') eq '1'; *selects only the .1 files;
put _infile_; *???;
infile dummy2 filevar=path filename=fname end=eof dsd;
filename = scan(fname,-2,'.\'); *fname does not exist in this data set at the end?;
length prefix $3.;
prefix = filename; *assumes prefix is always first 3 characters;
date = input (substr(filename,anydigit(filename)),mmddyy10.);
do while(not eof);
input obs name:$64.; ** Fields for type 1 files?;
output;
end;
format date date9.;
run;

In the end I get a data file with the variables you defined; filename, prefix, date, ... I know you wrote above to put in the field types for the data do I need to first declare them in the lenght statement above? It does not seem to be reading any information from the file itself only the file name so far.

Thanks.