Proc http error sas confused between & macro and url &&

[ilearnsas]

%let tod_dt=%sysfunc(intnx(year,%sysfunc(today()),0,S),yymmdd10.);

%put  &tod_dt;

filename frd_out "C:\My Documents\AdHoc\test.xls";

proc http

url="https://fred.stlouisfed.org/graph/fredgraph.xls?bgcolor=%23e1e9f0&chart_type=line&drp=0&fo=open%20sa..."&tod_dt."&line_color=%234572a7&link_values=false&line_style=solid&mark_type=none&mw=3&lw=2&ost=-99999&oet=99999&mma=0&fml=a&fq=Weekly%2C%20Ending%20Wednesday&fam=avg&fgst=lin&fgsnd=2009-06-01&line_index=1&transformation=lin&vintage_date="&tod_dt."&revision_date="&tod_dt."&nd=1955-08-10"

method="get"

out=frd_out;

run;

I think what is happening is that sas is getting confused between & macro and & in the url link.

I see the error(s) below. Below are the example errors:

WARNING: Apparent symbolic reference CHART_TYPE not resolved.

WARNING: Apparent symbolic reference CHART_TYPE not resolved.

WARNING: Apparent symbolic reference DRP not resolved.

2                                                          The SAS System                            20:01 Thursday, October 3, 2019

WARNING: Apparent symbolic reference FO not resolved.

WARNING: Apparent symbolic reference GRAPH_BGCOLOR not resolved.

WARNING: Apparent symbolic reference HEIGHT not resolved.

WARNING: Apparent symbolic reference HEIGHT not resolved.

WARNING: Apparent symbolic reference MODE not resolved.

Could you pls let me know what we can try instead to make this proc http download work? Thanks.

[Tom]

Do actually have any macro variables you ARE trying to resolve?

If not then just use single quotes instead of double quotes and the macro process will ignore the content.

[ilearnsas]

So single quotes for the & in the url link and no single quotes for the sas macro? Is that correct? Thanks. 

[Tom]

---

@ilearnsas wrote:

So single quotes for the & in the url link and no single quotes for the sas macro? Is that correct? Thanks. 

---

Not exactly that simple.  The macro processor will ignore text inside of single quotes. But you want to build the URL from some static parts and some dynamic parts.  You could add some macro quoting around the &'s that you don't want the macro processor to evaluate.  But it is going to be much easier to write/understand/edit if you just build the string as DATA instead of macro code.  You can then transfer the string to a macro variable and use that in your generated code.

[Astounding]

As you can see, macro language is trying to resolve expressions like &chart_type as if they were macro variables.  

As a long shot, I first have to ask the question ... would the syntax for PROC HTTP permit a space, such as changing from &chart_type to & chart_type ?

Assuming the answer is "no", I would suggest using a DATA step to write the code to a file.  That makes it easy to write out many pieces of code to the file.  As you do that, remember the previous comment:  single quotes prevent all macro activity, while double quotes permit resolution of &tod_dt.  The first time, inspect the file to make sure that the code was written properly.  Then %include the file that holds the code.

[Tom · Accepted solution]

No wonder SAS got confused. It really confused me trying to read that URL.

It is much easier to work with text in data steps instead of macro code. Generate the URL from its parts and then you can store it in a macro variable with single quotes around the outside and pass to the proc.

Note that intnx('year',today(),0,'s') is going to return the same value as today().  That is what the S or SAME option does.

data _null_;
 length url $20000 ;
 tod=put(today(),yymmdd10.);
 url = cats
('https://fred.stlouisfed.org/graph/fredgraph.xls'
,'?bgcolor=%23e1e9f0'
,'&chart_type=line'
,'&drp=0'
,'&fo=open%20sa...',tod
,'&line_color=%234572a7'
,'&link_values=false'
,'&line_style=solid'
,'&mark_type=none'
,'&mw=3'
,'&lw=2'
,'&ost=-99999'
,'&oet=99999'
,'&mma=0'
,'&fml=a'
,'&fq=Weekly%2C%20Ending%20Wednesday'
,'&fam=avg'
,'&fgst=lin'
,'&fgsnd=2009-06-01'
,'&line_index=1'
,'&transformation=lin'
,'&vintage_date=',tod
,'&revision_date=',tod
,'&nd=1955-08-10'
);
 call symputx('tod',tod);
 call symputx('url',quote(trim(url),"'"));
run;

filename frd_out "C:\My Documents\AdHoc\test.xls";
proc http url=&url method="get" out=frd_out;
run;

[ilearnsas]

Thank you. I tried this and it gives me the note below:

68 filename frd_out "/workspace/test.xls";
69 proc http url=&url method="get" out=frd_out
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
70 proxyhost="&proxy_host."
71 proxyport=&port;
72 run;

Checked to see the quotation marks and they are all balanced. Not sure where the issue is coming from. 

Upon opening the file- it is empty. The file is still being created but its just empty. When i open the excel file-test it says "The file format and extension of test.xls dont match. The file could be corrupted or unsafe. Unless you trust this source dont open it. Do you want to open it anyway"

[Astounding]

You're well on your way here.  Not looking to interfere (you're in good hands), but this might speed up the debugging.

Messages about the length of the quoted string can be ignored.

There is an error in this section:

,'&vintage_date=',tod
,'&revision_date=',tod
,'&nd=1955-08-10'

Your original syntax included double quotes around TOD.  If they belong there, you need to add them:

,'&vintage_date="',tod
,'"&revision_date="',tod
,'"&nd=1955-08-10'

[Tom]

,'&vintage_date=',quote(tod)
,'&revision_date=',quote(tod)
,'&nd=1955-08-10'

[Tom]

There is an option to turn off the warning about strings that are longer than 262 characters.

The file probably has the error message from the site. Look at it with a text editor.  Or just use a data step to read it and dump it to the log.

For example this data step will read the first 10 "lines" from the file and dump them to the log using the LIST command.

data _null_;
  infile "/.../test.xls" obs=10;
  input;
  list;
run;

[FreelanceReinh]

@ilearnsas: Did you expand the ellipsis ("...") in one of the strings?

That is, replace this line

,'&fo=open%20sa...',tod

by something like

,'&fo=open%20sans'
,'&graph_bgcolor=%23ffffff'
,'&height=450'
,'&mode=fred'
,'&recession_bars=on'
,'&txtcolor=%23444444'
,'&ts=12'
,'&tts=12'
,'&width=1168'
,'&nt=0'
,'&thu=0'
,'&trc=0'
,'&show_legend=yes'
,'&show_axis_titles=yes'
,'&show_tooltip=yes'
,'&id=WPRIME'
,'&scale=left'
,'&cosd=1955-08-10'
,'&coed=',tod