topic Re: Macro List and Using IN in SAS Programming

Macro List and Using IN

BCNAV — Thu, 02 Apr 2020 13:39:39 GMT

This is probably something simple. I have a dataset that has one variable in it called AERODROME. It contains airport IDs as characters. For example: CYYZ, EGGL, etc.

I then use:

data _NULL_;
set EGTASK.AERODROME_NORTH_CDA;
call symput('AERODROME_N',AERODROME);
run;

in order to create a macro variable called AERODROME_N that is now a list of all the airport IDs.

I then am trying the following:

PROC SQL;
CREATE TABLE EGTASK.TERM_NORTH AS
SELECT *
FROM IBS_COMB.ENROUTE_APPEND t1
WHERE depart IN ("&AERODROME_N") or dest IN ("&AERODROME_N")
ORDER BY flightdate;
QUIT;

I am just trying to get all records from IBS_COMB_ENROUTE that have a departure or destination in the macro list. What I get back is an empty dataset which I should not.

Any insight? Thanks in advance.

Re: Macro List and Using IN

Kurt_Bremser — Thu, 02 Apr 2020 13:47:10 GMT

@BCNAV wrote:

This is probably something simple. I have a dataset that has one variable in it called AERODROME. It contains airport IDs as characters. For example: CYYZ, EGGL, etc.

I then use:

data _NULL_;
set EGTASK.AERODROME_NORTH_CDA;
call symput('AERODROME_N',AERODROME);
run;

in order to create a macro variable called AERODROME_N that is now a list of all the airport IDs.

No, it's not. It only contains the value from the last observation read from the dataset. You do not need a macro variable AT ALL(!). You use a sub-select:

proc sql;
create table term_north as
select *
from ibs_comb.enroute_append
where
  depart in (select aerodrome from aerodrome_north_cda) or
  dest in (select aerodrome from aerodrome_north_cda)
order by flightdate;
quit;

Re: Macro List and Using IN

PaigeMiller — Thu, 02 Apr 2020 13:48:25 GMT

@BCNAV wrote:

This is probably something simple. I have a dataset that has one variable in it called AERODROME. It contains airport IDs as characters. For example: CYYZ, EGGL, etc.

I then use:

data _NULL_;
set EGTASK.AERODROME_NORTH_CDA;
call symput('AERODROME_N',AERODROME);
run;

in order to create a macro variable called AERODROME_N that is now a list of all the airport IDs.

I don't think &AERODROME_N contains the list of all the airport IDs. It contains the ID of the very last observation in EGTASK.AERODROME_NORTH_CDA.

To modify your code (and extra benefit, no macro variables needed), this seems like it would work

PROC SQL;
CREATE TABLE EGTASK.TERM_NORTH AS
        SELECT *
        FROM IBS_COMB.ENROUTE_APPEND t1
        WHERE depart IN (select distinct aerodrome from egtask.aerodrome_north_cda)
        or dest IN (select distinct aerodrome from egtask.aerodrome_north_cda)
        ORDER BY flightdate;
QUIT;

Re: Macro List and Using IN

Kurt_Bremser — Thu, 02 Apr 2020 13:51:22 GMT

Probably the fastest method is a data step with a hash:

data term_north;
set ibs_comb.enroute_append;
if _n_ = 1
then do;
  declare hash h (dataset:"aerodrome_north_cda");
  h.definekey("aerodrome");
  h.definedone();
end;
if h.find(key:depart) = 0 or h.find(key:dest) = 0;
run;

Re: Macro List and Using IN

Tom — Thu, 02 Apr 2020 13:58:59 GMT

Why create the macro variable at all?
Does your dataset (EGTASK.AERODROME_NORTH_CDA) only have one observation? If not then only the value from the last observation will be in your macro variable.

Your later code will only work as written if the macro variable is either just one value:

CYYZ

Or if there is more than one value it needs to have quotes in the middle, but not at the end

CYYZ" "EGGL

So that when you code adds the quotes on the outside you get something like:

"CYYZ" "EGGL"

Note: SAS doesn't care whether you use spaces or commas between the values in the list.

If you do want to put the list of values into a macro variable it is easier to use PROC SQL. You should just put the quotes in when you make the macro variable.

proc sql noprint;
  select quote(trim(AERODROME))
    into :AERODROME_N separated by ' '
    from EGTASK.AERODROME_NORTH_CDA
  ;
quit;

Re: Macro List and Using IN

ballardw — Thu, 02 Apr 2020 15:03:41 GMT

In addition to the comments of others about the need and content, even if your macro variable did contain a list it would have been used incorrectly in the sql.

Consider the case of AERODROME_N actually equaling the string value of CYYZ EGGL such as might be done with

%let AERODROME_N=CYYZ EGGL;

(Hint: this is one way to test your code without the faulty macro variable)

Then the code

PROC SQL;
CREATE TABLE EGTASK.TERM_NORTH AS
        SELECT *
        FROM IBS_COMB.ENROUTE_APPEND t1
        WHERE depart IN ("&AERODROME_N") or dest IN ("&AERODROME_N")
        ORDER BY flightdate;
QUIT;

Would resolve to:

PROC SQL;
CREATE TABLE EGTASK.TERM_NORTH AS
        SELECT *
        FROM IBS_COMB.ENROUTE_APPEND t1
        WHERE depart IN ("CYYZ EGGL") or dest IN ("CYYZ EGGL")
        ORDER BY flightdate;
QUIT;

Which would be logically incorrect as there is only a single value to find that would have "all" the airports.

The IN to work as intended would have to look like

        WHERE depart IN ("CYYZ" "EGGL") or dest IN ("CYYZ" "EGGL")

to find individual airports.

Another approach IF my following assumptions hold:

1) the data set EGTASK.AERODROME_NORTH_CDA contains some sort of regional group of locations , i.e. North

2) you have a number of different groups that you use frequently

3) the memberships of the groups changes infrequently

4) (and very important) no location is in more than one list.

If so then I might be tempted to make a format that has something like

proc format library=work;
value $areoregion
'CYYZ', 'EGGL' = 'North'
'BDDR', 'CCCP' = 'South'
'SCCA', 'FIDE' = 'Central'
;
run;

And the SQL could be modified to

PROC SQL;
CREATE TABLE EGTASK.TERM_NORTH AS
        SELECT *
        FROM IBS_COMB.ENROUTE_APPEND t1
        WHERE put(depart,$areoregion)='North' or put(dest,$areoregion.) = 'North')
        ORDER BY flightdate;
QUIT;

This may also gain some flexibility that may be needed that would require much more coding with extra sub-queries. Consider the following:

PROC SQL;
CREATE TABLE EGTASK.TERM_NORTH AS
        SELECT *
        FROM IBS_COMB.ENROUTE_APPEND t1
        WHERE put(depart,$areoregion)='North' AND put(dest,$areoregion.) = 'South')
        ORDER BY flightdate;
QUIT;

This would identify records for departure from one region and destination in another specific region.

Or using IN operator such as

        WHERE put(depart,$areoregion) in ('North' 'Central') AND put(dest,$areoregion.) = 'South')

depart from multiple regions and destination in one region .

PS the icon </> or "running man" creates code boxes to keep the forum from reformatting code and Log entries.