Create multiple %Let statements from mother strings and database or INTNX evaluation

[texasmfp]

I need to generate multiple %LET= statements based on a character string and, I have 2 variations of this issue:

Variation 1

For example, I have %LET COUNTRY=India;

I want to use the value of COUNTRY (India) to generate additional %LET statements such as:

%LET COUNTRY1=IND;

%LET COUNTRY2=IN;

%LET COUNTRY3="3457BunDD5";

I have a dataset that holds the values of COUNTRY1, COUNTRY2, and COUNTRY3 for each COUNTRY value.  I am familiar with using the EVAL function to resolve the value of a %LET statement, but only with numbers for example:

%let a=&size*6;
%let eval_a=%eval(&a);

Eval with strings is a whole new ball of wax, let alone with a database lookup.  So, for Variation 1, I want SAS to evaluate the &COUNTRY field and generate those %LET statements after looking them up in the database.

Variation 2

I also have %LET DATE1=2023-03; and %LET DATE2=2024-02; (string representations of March 2023 and February 2024, respectively)

I want to use the value of DATE1 and DATE2 to generate new %LET statements

%LET begin=03/01/2023; (i.e., the first day of the month/year in DATE1 as a string)

%LET end=02/29/2024; (i.e., the last day of the month/year in DATE2 - happens to be a leap day/year as a string)

%LET start=2023-01; (i.e., the first month of the year in DATE1 as a string)

%LET finish=2023-12; (i.e., the last month of the year in DATE1 as a string)

I am guessing that I need to take these string representations of a year and month and

1) convert these strings into a SAS date,

2)  use the INTX function to generate the first and last days for example:

data _null_;
d = '01FEB2024'd;
format d date10.;
d = intnx('month',d,1)-1;
put d;
run;

and 3) convert these back to character strings in a mm/dd/yyyy format and generate a %LET statement that resolves to that string.

I appreciate any suggestions.  Thanks.

[Patrick]

You are asking quite a few questions here. It's normally best to ask only one question per post so things don't get too messy.

For your dates consider below two options.

%let date_seed=2023-03;

/* option 1 */
%let date_start_mth=%sysfunc(inputn(&date_seed-01,yymmdd10.),mmddyy10.);
%let date_end_mth=%sysfunc(intnx(month,%sysfunc(inputn(&date_seed-01,yymmdd10.)),0,e),mmddyy10.);

%put &=date_start_mth;
%put &=date_end_mth;

/* option 2 */
data _null_;
  sas_dt_val=input("&date_seed.-01",yymmdd10.);
  call symputx('date_start_mth',put(sas_dt_val,mmddyy10.));
  call symputx('date_end_mth',  put(intnx('month',sas_dt_val,0,'e'),mmddyy10.));
run;
%put &=date_start_mth;
%put &=date_end_mth;

I feel for your other questions some more detail would be required to propose something to you that's actually useful.

You are talking about datasets and database and lookup. I suggest you explain us a bit more in detail what you actually have and what you are trying to achieve.

And for below question you would need to share the rule (logic) with us how you get from India to a string 3457BunDD5. But also here it's may-be worth you explain us first what you really got and what you need; The bigger picture like how your source data looks like and what end result you're after. There are often alternative approaches that don't require macro code at all.

For example, I have %LET COUNTRY=India;

I want to use the value of COUNTRY (India) to generate additional %LET statements such as:

%LET COUNTRY1=IND;
%LET COUNTRY2=IN;
%LET COUNTRY3="3457BunDD5";

%LET COUNTRY1=IND;

%LET COUNTRY2=IN;

%LET COUNTRY3="3457BunDD5";

[texasmfp]

Thanks Patrick.  Here is some more detail.  I need these variations of a country because I am doing a series of PROC HTTP calls and, different websites have different naming conventions for referencing a country in the structure of the API call string.  I use the %LET= statement to generate a section-specific code that is plugged into a string that is then fed into a PROC HTTP call (see code below).  Rather than manually edit the subsequent calls throughout the program, each with their unique variation of the code for India, I just want to edit a single country code at the top (let's call that the mother code) and then have SAS generate the multiple "daughter codes" for the geographic nomenclature used in the subsequent API calls to different websites. 

For example, for one call, that site's country code for India is a 2-digit string: IN.  So I need to take India at the top of the program and generate the appropriate country code for this call.  However, another site uses IND to represent India, while a 3rd site uses a long string in quotes.  So, in constructing the string for those sites, I need the variable to equal IND or "3457BunDD5"  Same thing for the dates, different API strings will require the start/end dates to be in a different format.  I apologize if those are two different questions warranting 2 posts - to me they are 2 variations of the same issue.

%LET country2=IN;
%LET start=2019;
%LET end=2024;

data _null_;
start="&start";
end="&end";
reporter="&country2";

length urltest $5000 ;
 urltest = cats
('http://dataservices.imf.org/REST/SDMX_xml.svc/CompactData/IFS/M.'
,reporter
,'.PCPI_IX.?startPeriod='
,start
,'&endPeriod='
,end
,'');

call symputx('urltest',quote(trim(urltest),"'"));
put urltest $char.; 
run;

filename out5 "e:\sas data\out5.xml";
proc http
url=&urltest
method="get" out=out5;
run;

This code will yield an example of my database with just two obs.

data country_Codes;
length COUNTRY $50 COUNTRY1 $3 COUNTRY2 $2 COUNTRY3 $25;
input COUNTRY $ COUNTRY1 $COUNTRY2 $ COUNTRY3 $;
cards;
Japan JPN JP "53f7368397f5f46"
India IND IN "3457BunDD5"
run;

[Patrick]

@texasmfp I consider it most of the time as easier (especially when it comes to debugging) to create a table for data driven code generation and then use this data to generate and execute the actual code. 

Below a working example generating the http call you shared.

data driver;
  infile datalines truncover dsd dlm=',';
  input url:$300. param_name:$50. param_val:$50.;
  datalines;
http://dataservices.imf.org/REST/SDMX_xml.svc/CompactData/IFS/M.IN.PCPI_IX.,startPeriod,2019
http://dataservices.imf.org/REST/SDMX_xml.svc/CompactData/IFS/M.IN.PCPI_IX.,endPeriod,2024
;

proc sort data=driver;
  by url;
run;

/* create temporary file for generated code */
filename codegen temp;
data _null_;
  stop;
  file codegen;
run;

data _null_;
  /* write generated code to temp file */
/*  file codegen;*/

  /* for development: write generated code to print destination */
  file print;

  set driver;
  by url;
  length url_all $1000;
  retain url_all;
  if cmiss(param_name, param_val)=0 then url_all=catx('&',url_all,catx('=',urlencode(strip(param_name)),urlencode(trim(param_val))));
  if last.url then
    do;
      n+1;
      url_all=catx('?',url, url_all);
      put
        '%let ind=' n z4. ';' /
        '%let out_path=%sysfunc(pathname(work))\out_&ind..xml;' / 
        'filename out_&ind "&out_path";' /
        'proc http ' /
        "  url='" url_all +(-1) "' " /
        '  method="get"  ' /
        '  out=out_&ind' /
        '  clear_cache ' /
        '  ; ' /
        'run; ' /
        ;
      put 
        'filename map temp;' /
        'libname out_&ind xmlv2 "&out_path" automap=replace xmlmap=map;' /
        ;
      call missing(url_all);
    end;
run;

/* execute generated code */
%include codegen / source2;

With above script the following code gets generated:

%let ind=0001;                                                                                                                      
%let out_path=%sysfunc(pathname(work))\out_&ind..xml;                                                                               
filename out_&ind "&out_path";                                                                                                      
proc http                                                                                                                           
  url='http://dataservices.imf.org/REST/SDMX_xml.svc/CompactData/IFS/M.IN.PCPI_IX.?startPeriod=2019&endPeriod=2024'                 
  method="get"                                                                                                                      
  out=out_&ind                                                                                                                      
  clear_cache                                                                                                                       
  ;                                                                                                                                 
run;                                                                                                                                
filename map temp;                                                                                                                  
libname out_&ind xmlv2 "&out_path" automap=replace xmlmap=map; 

...and of course how you create the driver table is up-to-you and if there is some logic to the parameters then you can of course also generate the rows instead of having to define them explicitly as data.

Here a variation of above with a bit more logic to create the driver table and generating two http calls.

Spoiler

data sites;
  infile datalines truncover dsd dlm=',';
  input site_id url:$300.;
  datalines;
1,http://dataservices.imf.org/REST/SDMX_xml.svc/CompactData/IFS/M.IN.PCPI_IX.
;
run;

data parameters;
  infile datalines truncover dsd dlm=',';
  input site_id param_group param_name:$50. param_val:$50.;
  datalines;
1,1,startPeriod,2019
1,1,endPeriod,2024
1,2,startPeriod,2020
1,2,endPeriod,2021
;

data driver;
  merge sites(in=ina) parameters;
  by site_id;
  if ina;
run;

proc sort data=driver;
  by site_id param_group;
run;

/* create temporary file with generated code */
filename codegen temp;
data _null_;
  stop;
  file codegen;
run;

data _null_;
  /* write generated code to temp file */
  file codegen;

  /* for development: write generated code to print destination */
/*  file print;*/

  set driver;
  by site_id param_group;
  length url_all $1000;
  retain url_all;
  if cmiss(param_name, param_val)=0 then url_all=catx('&',url_all,catx('=',urlencode(strip(param_name)),urlencode(trim(param_val))));
  if last.param_group then
    do;
      n+1;
      url_all=catx('?',url, url_all);
      put
        '%let ind=' n z4. ';' /
        '%let out_path=%sysfunc(pathname(work))\out_&ind..xml;' / 
        'filename out_&ind "&out_path";' /
        'proc http ' /
        "  url='" url_all +(-1) "' " /
        '  method="get"  ' /
        '  out=out_&ind' /
        '  clear_cache ' /
        '  ; ' /
        'run; ' /
        ;
      put 
        'filename map temp;' /
        'libname out_&ind xmlv2 "&out_path" automap=replace xmlmap=map;' /
        ;
      call missing(url_all);
    end;
run;

/* execute generated code */
%include codegen / source2;

[PaigeMiller]

Regarding your question about dates in macro variables, it is critical that we know how you are going to use these macro variables. Typically, when you are performing logical or arithmetic operations on macro variables, you do NOT want macro variables to be formatted. See Maxim 28.

So if you are going to use the macro variable for example as finding appropriate dates with something like this

where date between &date_start_mth and &date_end_mth

or similar in SQL or in a DATA step, you DO NOT want these dates formatted. SAS works just fine with the unformatted dates, and requires more work if you want to use formatted dates. So borrowing from @Patrick 

data _null_;
  sas_dt_val=input("&date_seed.-01",yymmdd10.);
  call symputx('date_start_mth',sas_dt_val));
  call symputx('date_end_mth',intnx('month',sas_dt_val,0,'e'));
run;

this works, with much less typing. So please tell us how you are going to use these macro variables.

Even simpler, no formatting of macro variable dates needed at all, no macro variables needed either except for &date_seed.

data want;
     set have;
     sas_dt_val=input("&date_seed.-01",yymmdd10.);
     if intnx('month',date,0,'b')=sas_dt_val;
run;

So please tell us how you are going to use these macro variables.

[ballardw]

I would be strongly tempted to create custom formats for the Country1, 2 and 3 values. Especially as you say that you have all the information in a data set. You can fairly easily turn such as set into a control set to make the formats.

A small example of how you could use such formats (Note: you cannot end format names with digits so I have appended an _ at the end of each country. Personally I would also come up with better names ).

proc format;
value $country1_
'Japan'='JPN'
'India' ='IND'
;
value $country2_
'Japan'='JP'
'India' ='IN'
;
value $country3_
'Japan'='53f7368397f5f46'
'India' ='3457BunDD5'
;

run;

%let country=India;

%put use %sysfunc(putc(&country,$country1_.)) for the country1 value;
%put use %sysfunc(putc(&country,$country2_.)) for the country2 value;
%put use %sysfunc(putc(&country,$country3_.)) for the country3 value;

So you no longer have to every create multiple %let statements, just use the form as desired. Or use

%let with the %sysfunc code.

How to turn a data set like that into multiple formats. I have used different format names so you test whether these work the same as the previous manual Proc Format code generated formats.

data country_Codes;
length COUNTRY $50 COUNTRY1 $3 COUNTRY2 $2 COUNTRY3 $25;
input COUNTRY $ COUNTRY1 $COUNTRY2 $ COUNTRY3 $;
cards;
Japan JPN JP "53f7368397f5f46"
India IND IN "3457BunDD5"
run;

data country_cntlin;
   set country_codes;
   length start label $ 25;
   fmtname='Code1_';
   type='C';
   start=Country;
   label=Country1;
   output;
   fmtname='Code2_';
   type='C';
   start=Country;
   label=Country2;
   output;
   fmtname='Code3_';
   type='C';
   start=Country;
   label=Country3;
   output;
run;
/* need all the like Fmtname together*/
proc sort data= country_cntlin;
   by fmtname start;
run;
proc format  cntlin=country_cntlin;
run;

[texasmfp]

@ballardw Thanks, but 1) that does not work and 2) I am trying to avoid hardcoding the values as the full database has about 200 countries and 5 possibly 6 fields - the 2 countries and 3 fields in my example are illustrative.

To reiterate, I have a series of API calls to retrieve and eventually aggregate data.  Unfortunately, every site uses a different convention to identify a country.  Some use, for example India to represent India, some use IND to represent India, some us a 4-digit number, and some use a long string of numbers and case-specific characters.  I am trying to avoid the need to manually change the site-specific nomenclature every time I run the program for a different country.  So I want to start with simple  %LET country=India; at the top generate the subsequent %LET country_x= site=-<specific nomenclature for India> in the remainder of the program.

In sum, I need to generate the first line based on the value I set in the top-of-the-program %LET country=India; statement.  BTW, thanks to Patrick's suggestion, the parallel issue but for generating dates is solved.

%LET country_a=IN;
%LET start=2019;
%LET end=2024;

For those who are asking how it is used (the global picture) it is this.  That %LET country_a=IN; statement that I want SAS to generate for me, feeds into a pre-API call statement that generates a url string, one where the country and dates change.

data _null_;
start="&start";
end="&end";
reporter="&country_a";

length urltest $5000 ;
 urltest = cats
('http://dataservices.imf.org/REST/SDMX_xml.svc/CompactData/IFS/M.'
,reporter
,'.PCPI_IX.?startPeriod='
,start
,'&endPeriod='
,end
,'');

call symputx('urltest',quote(trim(urltest),"'"));
put urltest $char.; 
run;

This url string is then fed into the PROC HTTP

filename out5 "e:\sas data\out5.xml";
proc http
 url=&urltest
 method="get" out=out5;
run;

I tested @ballardw suggested language, and it does not work.  So, please help me understand how I get from

%LET country= xxxxx;

to

%LET country_a=XX;

%LET country_b=XXX;

%LET country_c="7Xx56xXX";  etc...  If not these actual %LET statements, then create the global variables with the calculated values.

BTW, in the above URL, the value for India is IN.  Thanks

[ballardw]

---

@texasmfp wrote:

@ballardw Thanks, but 1) that does not work and 2) I am trying to avoid hardcoding the values as the full database has about 200 countries and 5 possibly 6 fields - the 2 countries and 3 fields in my example are illustrative.

How does it not work? You said you had a data set with the values needed. The formats are one of the easiest ways to turn one value such as India into multiple others for used.

and 2)hardcoding the values as the full database

Do you mean you do not have a data set with all the values for all 200 countries? There is nothing "hardcoded" that uses your data set to create the format. And once a format is created an placed in a known library for the search path it is one of the faster ways to retrieve value sets like this.

[Quentin]

What do you mean when you say @ballardw 's example did not work?  Did you get an error message?  Can you show a small example of how you tried that approach (code and log)?  

Using formats was my first thought as well.  They're one straight-forward way to implement lookups.  So basically your input is "India" and you want to use that to look up the different ways that value might be coded for each API.  If you have the formats with the lookups, and you really want macro variables, you could do:

%let country=India;

%let country_a=%sysfunc(putc(&country,$country1_.));
%let country_b=%sysfunc(putc(&country,$country2_.));
%let country_c=%sysfunc(putc(&country,$country3_.));

Another common lookup is method is to use  hash table.  You could make a hash table where the key in is the country name, and the values are the different encodings of the country name.  

But then again As Tom has mentioned, you could probably do this without macro variables at all.  If you have all the "driver" data in datasets (country name, country codes, date range),  you could generate your PROC HTTP step with CALL EXECUTE, or PUT statements writing a .sas file that is %included.

If you're going to use macro variables, I would probably make a single macro which executes on PROC HTTP call, so it would be something like:

%macro APIcall(
  file=/*output xml file*/
  ,country=
  ,start=
  ,end=
  );

filename myout "&file";

proc http 
  URL="/*URL built from hardcoded parts and values from macro parameters*/"
  method="get" out=myout;
run;

filename myout clear;

%mend ;

With something like that you can focus on getting the macro working for one call (e.g. dealing with repeat calls if the API call fails, etc).  

And then if you have a dataset with values for input parameters, you can use that to generate a bunch of macro calls using CALL EXECUTE.

[Tom]

Why do you need ANY macro variables if you already have the data in a DATASET?

If you want to generate code from data it is probably going to be MUCH MUCH easier to do it with a DATA step than with macro logic.  First thing is it is much easier to debug.  It is easier to scale.  And if you generate the code to a FILE you can use the full power of the PUT statement.

[Reeza · Accepted solution]

data country_lookup;
    infile cards dlm=',';
    length country3 $12.;
    input country $ country1 $ country2 $ country3 $;
    cards;
India, IND, IN, 3457BunDD5
;
run;

%let country=India;
%LET DATE1=2023-03;
%LET DATE2=2024-02;

data _null_;
    set country_lookup;
    where country="&country";
    call symputx('country1', country1);
    call symputx('country2', country2);
    call symputx('country3', quote(trim(country3)));
run;

%put &country1.;
%put &country2.;
%put &country3.;

data _null_;
    date1=input("&date1.-01", yymmdd10.);
    date2=input("&date2.-01", yymmdd10.);
    format date1 date2 date9.;
    call symputx('begin', put(intnx('month', date1, 0, 'b'), mmddyys10.));
    call symputx('end', put(intnx('month', date2, 0, 'e'), mmddyys10.));
    call symputx('start', catx("-", year(date1), '01'));
    call symputx('finish', catx("-", year(date1), '12'));
run;

%put begin=&begin.;
%put end=&end.;
%put start=&start.;
%put finish=&finish.;

---

@texasmfp wrote:

I need to generate multiple %LET= statements based on a character string and, I have 2 variations of this issue:

Variation 1

For example, I have %LET COUNTRY=India;

I want to use the value of COUNTRY (India) to generate additional %LET statements such as:

%LET COUNTRY1=IND;

%LET COUNTRY2=IN;

%LET COUNTRY3="3457BunDD5";

I have a dataset that holds the values of COUNTRY1, COUNTRY2, and COUNTRY3 for each COUNTRY value.  I am familiar with using the EVAL function to resolve the value of a %LET statement, but only with numbers for example:

%let a=&size*6;
%let eval_a=%eval(&a);

Eval with strings is a whole new ball of wax, let alone with a database lookup.  So, for Variation 1, I want SAS to evaluate the &COUNTRY field and generate those %LET statements after looking them up in the database.

Variation 2

I also have %LET DATE1=2023-03; and %LET DATE2=2024-02; (string representations of March 2023 and February 2024, respectively)

I want to use the value of DATE1 and DATE2 to generate new %LET statements

%LET begin=03/01/2023; (i.e., the first day of the month/year in DATE1 as a string)

%LET end=02/29/2024; (i.e., the last day of the month/year in DATE2 - happens to be a leap day/year as a string)

%LET start=2023-01; (i.e., the first month of the year in DATE1 as a string)

%LET finish=2023-12; (i.e., the last month of the year in DATE1 as a string)

I am guessing that I need to take these string representations of a year and month and

1) convert these strings into a SAS date,

2)  use the INTX function to generate the first and last days for example:

data _null_;
d = '01FEB2024'd;
format d date10.;
d = intnx('month',d,1)-1;
put d;
run;

and 3) convert these back to character strings in a mm/dd/yyyy format and generate a %LET statement that resolves to that string.

I appreciate any suggestions.  Thanks.

---

[Tom]

If you have the conversion information in a dataset then the easiest thing to do is make formats from the dataset.

So if you have a dataset, let's call it COUNTRY, with the variables COUNTRY1, COUNTRY2 and COUNTRY3 you can use it to generate two character formats.  Let's call them $COUNTRYB and $COUNTRYC (format names cannot end in a digit because then you would not be able to tell where the name ends and the width begins when trying to use the format name to generate an actual format specification.).  So you could make a dataset in the style need by PROC FORMAT like this:

data formats;
  set country;
  length fmtname $32 start $40 label $40 ;
  start=country1;
  fmtname='$COUNTRYA';
  label=country2;
  output;
  fmtname='$COUNTRYB';
  label=country3;
  output;
run;
proc sort; by fmtname start; run;
proc format cntlin=formats; run;

Now you can use %SYSFUNC() macro function (or probably better the %QSYSFUNC() so the value has macro quoting added) to call the PUTC() function to convert values from COUNTRY1 to COUNTRY2 or COUNTRY3.

%LET COUNTRY1=IND;
%LET COUNTRY2=%qsysfunc(putc(&country1,$countryB.));
%LET COUNTRY3=%qsysfunc(putc(&country1,$countryC.));

If you really need the quotes in the value of COUNTRY3 you could possibly add them into the value of COUNTRY3 in the COUNTRY dataset.  Or add them into the value of LABEL that is use to create the format.  Or add them in the %LET.  Or add them at the point you use the macro variable later.

To manipulate dates in macro code you can also use %SYSFUNC().  Are you sure you want to generate dates in that confusing MDY style?  50% of your audience might think you meant DMY style.  Anyway just use the proper format when generating the date.  The first step is to convert your year month strings into actual dates.

%LET DATE1=2023-03; 
%LET DATE2=2024-02; 

%let sasdate1=%sysfunc(inputn(&date1.-01,yymmdd10.));
%let sasdate2=%sysfunc(inputn(&date2.-01,yymmdd10.));
%LET begin=%sysfunc(putn(&sasdate1,mmddyy10.));
%LET end=%sysfunc(intnx(month,&sasdate2,0,e),mmddyy10.);
%LET start=%sysfunc(intnx(year,&sasdate1,0),yymmd7.);
%LET finish=%sysfunc(intnx(year,&sasdate1,0,e),yymmd7.);

Are you sure FINISH shouldn't be using the end of the year from DATE2?  Because now with this example it is before month indicated by END.

[texasmfp]

I want to thank all contributors for their suggestions; especially @Reeza and @Patrick.  I cobbled together pieces of code from multiple responses and have developed a bespoke solution.  Again, I thank the SAS Community for this forum.   

[Quentin]

Glad you got it working. In order to "close" this question, please select an answer to mark as the accepted solution.  And feel free to post your own bespoke solution and accept that.