CODEWITHSUNDEEP
sassas-macro
Craig Davison
Craig Davison

Reputation: 43

SAS Macro variable escaping apostrophe in variable name Proc Http

I have been working on this for 3 days now and have tried all I can think of including %str(),%bquote(), translate() and tranwrd() to replace single apostrophe with double apostrophe or %’

The below data step and macro work fine until I hit a last name which contains an apostrophe e.g. O’Brien. I then encounter syntax errors due to un closed left parentheses. The below code I have left what I thought was closest to working with the tranwrd included.

Any assistance you can provide is greatly appreciated.

%macro put_data (object1,id);
Proc http
method=“put”
url=“https://myurl/submissionid/&id”
in=&object1;
Headers “content-type”=“application/json”;
Run;
%mend;

data _null_;
Set work.emp_lis;
call execute(catt(‘%put_data(‘,’%quote(‘’{“data”:{“employeeName”:”’,tranwrd(employeeName,”’’”,”’”),’”}}’’),’,id,’)’));
run;

Craig

Upvotes: 0

Views: 699

Answers (3)

Tom
Tom

Reputation: 51611

Just use actual quotes and you won't have to worry about macro quoting at all.

So if your macro looks like this:

%macro put_data(object1,id);
proc http method="put"
  url="https://myurl/submissionid/&id"
  in=&object1
;
  headers "content-type"="application/json";
run;
%mend;

Then the value of OBJECT1 would usually be a quoted string literal or a fileref. (There are actually other forms.) Looks like you are trying to generate a quoted string. So just use the QUOTE() function.

So if your data looks like:

data emp_lis;
  input id employeeName $50.;
cards;
1234 O'Brien
4567 Smith
;

Then you can use a data step like this to generate one macro call for each observation.

data _null_;
  set emp_lis;
  call execute(cats
    ('%nrstr(%put_data)('
    ,quote(cats('{"data":{"employeeName":',quote(trim(employeeName)),'}}'))
    ,',',id
    ,')'
    ));
run;

And your SAS log will look something like:

NOTE: CALL EXECUTE generated line.
1    + %put_data("{""data"":{""employeeName"":""O'Brien""}}",1234)

NOTE: PROCEDURE HTTP used (Total process time):
      real time           2.46 seconds
      cpu time            0.04 seconds

2    + %put_data("{""data"":{""employeeName"":""Smith""}}",4567)

NOTE: PROCEDURE HTTP used (Total process time):
      real time           2.46 seconds
      cpu time            0.04 seconds

Upvotes: 0

Craig Davison
Craig Davison

Reputation: 43

I was able to find issue with my code with the tranwrd statement was backwards and it needed to be moved to the proc sql create table statement. I also needed to wrap &object1 in %bquote. This was the final code that worked. When creating table wrap variables in tranwrd as below.

tranwrd(employeeName, “‘“,”’’”)

% macro put_data (object1,id);
Proc http
method=“put”
url=“https://myurl/submissionid/&id”
in=%bquote(&object1);
Headers “content-type”=“application/json”;
Run;
%mend;

data _null_;
Set work.emp_lis;
call execute(catt(‘%put_data(‘,’%quote(‘’{“data”:{“employeeName”:”’,employeeName,’”}}’’),’,id,’)’));
run;

Upvotes: 0

Richard
Richard

Reputation: 27508

There are a wide potential of problems in constructing or passing a json string in SAS macro. Proc JSON will produce valid json (in a file) from data and that file in turn can be specified as the input to be consumed by your web service.

Example:

data have;
  length id 8 name $25;
  input id& name&;
datalines;
1  Homer Simpson
2  Ned Flanders
3  Shaun O'Connor
4  Tom&Bob 
5  'X Æ A-12'
6  Goofy "McDuck"
;

%macro put_data (data,id);

filename jsonfile temp;

proc json out=jsonfile;
    export &data.(where=(id=&id));

    write values "data";
    write open object;
        write values "name" name;
    write close;
run;

proc http
    method="put"
    url="https://myurl/submissionid/&id"
    in=jsonfile
;
    headers "content-type"="application/json";
run;

%mend;

data _null_;
    set have;
    call execute(cats('%nrstr(%put_data(have,',id,'))'));
run;

Upvotes: 0

Related Questions