Download mutiple files with SAS macro proc download

[John178]

I am downloading many txt file from WRDS server. I can do one file at a time successfully. But the I have a list of 8000 paths of files in data A. I try to create macro variables from paths such as " /wrdslin/11551.txt, /wrdslin/1832555.txt, /wrdslin/18355557.txt", but the program return error. Could anyone please help me?
data B ;
set A;
length link link2 $ 500;
link=substr(Reference_Name_of_Complete_Repor, 1);
link2= cats('/wrdslin/', link);
keep link2;
run;

%let wrds=wrds-cloud.wharton.upenn.edu 4016;
options comamid=TCP remote=WRDS;
signon username=user password="xyz";

rsubmit;

* working well
proc download infile="/wrdslin/000000/6814/0000006814-95-000009.txt"
outfile="E:/test1.txt";

* not working

ERROR: Physical file does not exis

%let link3=link2;
proc download infile="&link3"
outfile="E:/&link3";
run;

[ballardw]

Hint: when you get an error message include the code as well as the error from the LOG. Not just the error message but the code that caused the error(s). Copy from the log, on the forum open a text box using the </> icon and paste the text.

The text box preserves the positions of diagnostic characters often provided by SAS to indicate the specific syntax problem. 

Include the full text. Typically the "file does not exist" error messages include the file text attempted.

It would likely help to provide an example of the contents of your data set A.

Some questions you need to resolve: You create data set B but do not use it anywhere. So why?

You have this line of macro code:

%let link3=link2;

I suggest that you examine the value of Link3 with something like this after that statement:

%put The value of Link3 is: &link3.;

Since I do not see any code that attempts to take the value of the data step variable Link2 and make it available to the %let statement, I suspect Link3 is blank.

[John178]

My link2 in data set B contains specific path as above. When I do for each path, it works. But when I try to create Macro to process the all the paths automatically, it has error :

195 %let link3=link2;
196 %put The value of Link3 is: &link3.;
The value of Link3 is: link2
197
198 rsubmit;
NOTE: Remote submit to WRDS commencing.

4 proc download infile="&link3"
WARNING: Apparent symbolic reference LINK3 not resolved.
5 outfile="&link3";
WARNING: Apparent symbolic reference LINK3 not resolved.
6 run;

NOTE: TEXT download in progress from infile=&link3 to outfile=&link3
ERROR: Physical file does not exist, /home/&link3.
NOTE: The SAS System stopped processing this step because of errors.

How should I create a Macro to solve my problem

[ballardw]

---

@John178 wrote:

My link2 in data set B contains specific path as above. When I do for each path, it works. But when I try to create Macro to process the all the paths automatically, it has error :

195 %let link3=link2;
196 %put The value of Link3 is: &link3.;
The value of Link3 is: link2
197
198 rsubmit;
NOTE: Remote submit to WRDS commencing.

4 proc download infile="&link3"
WARNING: Apparent symbolic reference LINK3 not resolved.
5 outfile="&link3";
WARNING: Apparent symbolic reference LINK3 not resolved.
6 run;

NOTE: TEXT download in progress from infile=&link3 to outfile=&link3
ERROR: Physical file does not exist, /home/&link3.
NOTE: The SAS System stopped processing this step because of errors.

How should I create a Macro to solve my problem

---

I say again: There is NOTHING in your code that links the data set variable link2 to the macro variable. Nothing. After the data step, i.e. the run statement, "link2" is no longer a data step variable and holds no values. The %let statement if attempted inside a data step does not "see" the value of the data step variable link2. That is just the way the macro processor works.

So the macro assignment you use

%let link3=link2; is assigning the literal text "link2" to the macro variable. That is what the %PUT shows. And why the path in the download references "link2" instead of the value of the data step variable link2.

To place the value of a data step variable into a macro variable you can use Call Symputx, or older Call Symput. But that would likely require creating a great many variables as otherwise the resulting macro variable would contain the value of only the last data set value.

Or you can use Proc SQL with the Select into place one or more values into a macro variable.

Or use a an alternate approach as @Patrick includes to create syntax statements placed in an execution stack to execute after the end of the data step.

Another approach would be to use the information in the data step to write to a text program file with the text to submit and use an %include statement to bring that written code into the session for execution.

[Kurt_Bremser]

Here you have a very good example why the final message is usually the least important.

The first "non-normal" message

WARNING: Apparent symbolic reference LINK3 not resolved.

points you to the real problem: macro variable link3 was never created.

Always debug from the top down. Start at the top of your log, and fix all extraneous messages; very often the first fix clears all subsequent issues.

[Patrick]

Code very similar to below should work for you.

data local.file_list;
  infile datalines truncover dlm=' ' dsd;
  input path :$80. file :$40.;
  datalines;
C:\temp\proc_download_test\remote test1.txt
C:\temp\proc_download_test\remote test2.txt
;

options autosignon;
rsubmit emp1 sascmd="!sascmd" inheritlib=(local work=remote); 

  data _null_;
    set local.file_list;
    length file_in file_out $120 cmd $400;
    file_in=catx('/',path,file);
    file_out=catx('/','C:\temp\proc_download_test\local',file);
    cmd=cats("proc download infile='",file_in,"' outfile='",file_out,"'; run;");
    call execute(cmd);
  run;

endrsubmit;