Help using Base SAS procedures

Reg:Upload data

Reply
Contributor
Posts: 66

Reg:Upload data

Hi

I will be getting data daily in excel ,the data is placed in the server and the excel is placed on the each day folder like,10/02/2011,11/02/2011
the excel structure is the same for each day now i want to automate it and create sasdataset with the dates ex Smiley Very Happyaily_10/02/2011 like that. how can i move from one folder to another folder
Super User
Posts: 9,687

Re: Reg:Upload data

Give you an example.
The code search all the sub-directory under c:\temp\ and convert xls file into csv file at respective sub-directory.
[pre]



filename _xls pipe 'dir c:\temp\*.xls /s';
data xls_file;
infile _xls length=len;
input whole $varying200. len;
retain directory;
length file_name $ 200;
if strip(whole) eq: 'c:\temp' then directory=scan(whole,1,' ');
else if scan(whole,-1,'.') eq 'xls' then do;
_name=scan(trim(whole),-1,' ');
file_name=catx('\',directory,_name);
output;
end;

keep file_name;
run;
data _null_;
set xls_file nobs=num_obs;
call symputx(cats('path',_n_),file_name);
call symputx(cats('name',_n_),scan(file_name,-2,'.\'));
call symputx('nobs',num_obs);
run;
%put _user_;
options mprint mlogic symbolgen;
%macro xls_to_csv;
%do i=1 %to &nobs;
proc import datafile="&&path&i" out=&&name&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
proc export data=&&name&i outfile="%scan(&&path&i,1,'.').csv " dbms=csv replace;run;
%end;
%mend ;

%xls_to_csv

[/pre]


Ksharp
Ask a Question
Discussion stats
  • 1 reply
  • 108 views
  • 0 likes
  • 2 in conversation