Here's something of an example of controlling file names for upload/download:
Step 1: I have a macro that generates one to many files based on the data available at the time. I'm using data from within a SAS data set to name my output files, but you could use date macros. I'm downloading, but this technique can also be used for uploading.
%MACRO Create_Files_By_Package (Package_ID= );
PROC EXPORT
DATA=WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30
(WHERE=(Package_ID = "&Package_ID"))
OUTFILE="/sasapps/soc/Package_Assigned_Meters/Mtrs_5DayIRR_LE30_&Package_ID..csv"
DBMS=CSV
REPLACE
;
RUN;
%Check_SYSERR;
%MEND;
/** For each unique Package_ID (as identified by FIRST.), call a macro to **/
/** create a CSV file from the SAS dataset for just that Package_ID **/
DATA _NULL_;
SET WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30;
BY Package_ID;
IF FIRST.Package_ID THEN
CALL EXECUTE ('%Create_Files_By_Package(Package_ID='||strip(Package_ID)||');');
RUN;
Step 2: I set some macro variables to control the download and then execute the Copy Files Task in my Enterprise Guide workflow.
%LET Datafile=Mtrs_5DayIRR*;
%LET Download_To=C:\Users\barbourj\Documents\Tech\SAS\Output;
%LET Download_From=/sasapps/soc/Package_Assigned_Meters/&Datafile..csv;
Note that here I'm using a wildcard so that all files matching Mtrs_5DayIRR* will be downloaded. For example, files
Mtrs_5DayIRR_LE30_WS2015_11.csv
Mtrs_5DayIRR_LE30_WS2015_12.csv
Mtrs_5DayIRR_LE30_WS2016_01.csv
Mtrs_5DayIRR_LE30_WS2016_02.csv
would all be downloaded.
Everything in my example is driven by the data in a SAS data set, but you could set yours up to be by date, time, etc.
Jim
Hi Jim
thank you, I think your proposal could be the solution, until we find macro variables support in the SASEguide Import data task. I've opened another track to work on a translation of the Copy Files Task from Chris for my French Customers. And start to build a demo to show them they can use this Custom Task to answer their need.
thanks again Jim
You are welcome, and good luck.
One thing that might be tricky. You will notice that I'm passing a normal SAS variable in my CALL EXECUTE statement. If you pass in a macro variable, there are conditions where it may not work properly due to the timing of the way the macro facility works in SAS. This is particularly true if there are macro variables inside your macro logic that are derived from the passed macro variable. In that case instead of:
DATA _NULL_;
SET WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30;
BY Package_ID;
IF FIRST.Package_ID THEN
CALL EXECUTE ('%Create_Files_By_Package(Package_ID='||strip(Package_ID)||');');
RUN;
You might want to try the following:
DATA _NULL_;
SET WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30;
BY Package_ID;
IF FIRST.Package_ID THEN
CALL EXECUTE (%NRSTR('%Create_Files_By_Package(Package_ID='||strip(&Working_Directory&Run_Date)||');'));
RUN;
I am not at work right now, so I don't have an example in front of me, but I think I have the syntax of the command right. The %NRSTR tells the macro facility not to resolve the passed macro variables and immediately begin execution. Instead, the variables are resolved, then any macro variables depending on the passed macro variables are resolved, and then execution begins.
Again, good luck,
Jim
P.S. If your data step that calls the macro is a long running step, you might want to use DOSUBL instead of CALL EXECUTE. The difference is that when using CALL EXECUTE, your code will not be executed until after your data step finishes whereas with DOSUBL, your code should begin immediate execution. I have not used DOSUBL a great deal but from reading Chris Hemedinger's blog, it appears to hold great promise. If you have a long running data step driving your CALL EXECUTE statements, you might want to experiment with DOSUBL.
PascalMaurice:
I'm at work now. Here (below) is a cut and paste from a functioning program. This may be helpful in terms of the syntax which can be a bit tricky. The syntax I included above isn't quite right. Better to use this actual working code as an example. This code reads through a list of tabs (worksheets) exported as text from two different Excel workbooks, importing each tab in turn (163 of them for each Excel file) into SAS. It compares each of the two sets tabs, a 100% sample vs. a 1% sample of data, looking for significant differences in the distribution of values and writes a report.
DATA _NULL_;
SET WORK.Tab_Names;
CALL EXECUTE ('%NRSTR(%Process_Excel_Tab(Tab_Name=' || strip(TabName) || ');)');
RUN;
%Check_SYSERR;
Inside the macro, I set up a number of parameters based on the name of the Tab that is passed into the macro. These parameters then control a variety of programs that process data based on the parameters.
%MACRO Process_Excel_Tab(Tab_Name=);
%LET DeBug = *;
%LET Data_Type = 100;
%LET FileIn = &DataLib&Tab_Name._&Data_Type;
%LET Data_Format = txt;
%LET Report_Title = &Tab_Name._&Data_Type;
OPTIONS SOURCE2;
%INCLUDE "&IncLib.Import_Program.sas";
OPTIONS NOSOURCE2;
%*Check_SYSERR;
TITLE "&Report_Title";
PROC CONTENTS DATA=Out.Percent_&Data_Type POSITION;
RUN &Control_Value;
%Check_SYSERR;
PROC PRINT DATA=Out.Percent_&Data_Type;
RUN &Control_Value;
%Check_SYSERR;
/*--------------------------------------------------------------------------*/
%LET DeBug = *;
%LET Data_Type = 1;
%LET FileIn = &DataLib&Tab_Name._&Data_Type;
%LET Data_Format = txt;
%LET Report_Title = &Tab_Name._&Data_Type;
OPTIONS SOURCE2;
%INCLUDE "&IncLib.Import_Program.sas";
OPTIONS NOSOURCE2;
%*Check_SYSERR;
TITLE "&Report_Title";
PROC CONTENTS DATA=Out.Percent_&Data_Type POSITION;
RUN &Control_Value;
%Check_SYSERR;
PROC PRINT DATA=Out.Percent_&Data_Type;
RUN &Control_Value;
%Check_SYSERR;
/*--------------------------------------------------------------------------*/
%LET DeBug = *;
%LET Data_Type1 = 1;
%LET Data_Type100 = 100;
%LET HTMLout = &HTMLlib&Tab_Name._&Data_Type1._Percent_vs_&Data_Type100..html;
OPTIONS SOURCE2;
%INCLUDE "&IncLib.Compare_Program.sas";
OPTIONS NOSOURCE2;
%*Check_SYSERR;
/*--------------------------------------------------------------------------*/
TITLE "&Tab_Name. -- &Data_Type1. Percent vs. &Data_Type100. Percent";
PROC CONTENTS DATA=Out.Percent_&Data_Type100._Final POSITION;
RUN &Control_Value;
%Check_SYSERR;
ODS HTML BODY="&HTMLout";
PROC PRINT DATA=Out.Percent_&Data_Type100._Final;
ID Range_Start Range_End ;
RUN &Control_Value;
%Check_SYSERR;
ODS HTML CLOSE;
PROC DELETE DATA=Out.Percent_&Data_Type1;
RUN &Control_Value;
%Check_SYSERR;
PROC DELETE DATA=Out.Percent_&Data_Type100;
RUN &Control_Value;
%Check_SYSERR;
PROC DELETE DATA=Out.Percent_&Data_Type100._Final;
RUN &Control_Value;
%Check_SYSERR;
%MEND Process_Excel_Tab;
I wrote this code in a hurry in one night, so it's not the most elegant, but perhaps this may be helpful as something of an example how to work with macro variables while processing tabs exported as text from Excel. I apologize if you already know all of this.
Jim
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.