BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BCNAV
Quartz | Level 8

I have a macro that grabs data as follows:

 

%macro airport_data (airport_list=);
%local n i;
%do n=1 %to %sysfunc(countw(&airport_list));
	%let i=%scan(&airport_list,&n);

some code

PROC SQL;
 		CREATE TABLE EGTASK.ARR_&i AS 
   		SELECT t1.DATE_UTC, 
     	       (year(t1.DATE_UTC)) AS Year, 
        	   (month(t1.DATE_UTC)) AS Month, 
        		t1.SUM_of__Calculation_Sum LABEL="Arrivals at Vancouver" AS &i._ARR
      	FROM MEANS_TMP t1
      	WHERE t1.DATE_UTC <= intnx('days',today(),-day(today()));
	QUIT;

	proc datasets nolist nodetails;
		delete means_tmp query_tmp SORTTEMPTABLESORTED_; run;
%end;
%mend;
%airport_data (airport_list=CYVR CYYC);

I would like to be able to label the data conditional on the airport code being used.

 

 

For example, if the airport_list=CYVR, I would like:

t1.SUM_of__Calculation_Sum LABEL="Arrivals at Vancouver" AS &i._ARR

 

Now if CYYC comes up I would like:

 

t1.SUM_of__Calculation_Sum LABEL="Arrivals at Calgary" AS &i._ARR

 

 

Can SAS use a conditional on the label?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

A label cannot be conditional. The label for a variable is a constant.

But the whole idea of a macro is to conditionally generate code. So yes your macro could conditionally generate the label based on the value of the macro variable that holds the name of the actual variable.

You could just use %IF/%THEN logic.

t1.SUM_of__Calculation_Sum
%if (&i = CYVR ) %then LABEL="Arrivals at Vancouver" ;
%else %if (&i=CYYC) %then LABEL="Arrivals at XXXX" ;
AS &i._ARR 

You might want to either pass in the names associated with the airport codes, or define a format, or have a table you can query to find the name.  So if you had a format named $AIRPORT then your code would look like this.

t1.SUM_of__Calculation_Sum LABEL="Arrivals at %sysfunc(putc(&i,$airport))" AS &i._ARR

If you needed to query a table then add another local macro varaible and populate it before the step where you need it. Make sure to intialize the new macro variable in case the query does not match any record in your metadata table.

proc sql noprint ;
  %let airport=&i;
  select airport into :airport trimmed 
    from code_to_airport
    where code="&i"
  ;
  create table egtask.arr_&i as 
    select t1.date_utc
         , year(t1.date_utc) as year
         , month(t1.date_utc) as month
         , t1.sum_of__calculation_sum label="Arrivals at &airport" as &i._arr
    from means_tmp t1
    where t1.date_utc <= intnx('month',today(),-1,'e')
  ;
quit;

 

 

 

 

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

A label cannot be conditional. The label for a variable is a constant.

But the whole idea of a macro is to conditionally generate code. So yes your macro could conditionally generate the label based on the value of the macro variable that holds the name of the actual variable.

You could just use %IF/%THEN logic.

t1.SUM_of__Calculation_Sum
%if (&i = CYVR ) %then LABEL="Arrivals at Vancouver" ;
%else %if (&i=CYYC) %then LABEL="Arrivals at XXXX" ;
AS &i._ARR 

You might want to either pass in the names associated with the airport codes, or define a format, or have a table you can query to find the name.  So if you had a format named $AIRPORT then your code would look like this.

t1.SUM_of__Calculation_Sum LABEL="Arrivals at %sysfunc(putc(&i,$airport))" AS &i._ARR

If you needed to query a table then add another local macro varaible and populate it before the step where you need it. Make sure to intialize the new macro variable in case the query does not match any record in your metadata table.

proc sql noprint ;
  %let airport=&i;
  select airport into :airport trimmed 
    from code_to_airport
    where code="&i"
  ;
  create table egtask.arr_&i as 
    select t1.date_utc
         , year(t1.date_utc) as year
         , month(t1.date_utc) as month
         , t1.sum_of__calculation_sum label="Arrivals at &airport" as &i._arr
    from means_tmp t1
    where t1.date_utc <= intnx('month',today(),-1,'e')
  ;
quit;

 

 

 

 

BrunoMueller
SAS Super FREQ

Best way would be to build a format based on the airport code, I am sure you have a data set that has airport codes and cities.

 

You then can use the format together with %SYSFUNC and the PUTC function within a macro.

 

Here is a simpel example

 

proc format;
  value $apcode
    "CYVR" = "Vancouver"
    "CYYC" = "Calgary"
  ;
run;

%let apcode = CYVR;
%put NOTE: &apcode is %sysfunc(putc(&apcode, $apcode.));

%let apcode = CYYC;
%put NOTE: &apcode is %sysfunc(putc(&apcode, $apcode.));
ballardw
Super User

By using "some code" you hide what the name of the variable is that holds the values of your Airport.

 

What I think you are doing may very well be handled by a custom format to use associate values of "Vancouver" with "CYVR" and then by group processing instead of macro loops or possibly a different report procedure with a where statement and by group.

 

An example with proc tabulate.

 

proc format library=work;
value $Airport
'CYVR' ='Vancouver'
'CYYC' ='Calgary'
;
run;

data junk;
   do Airport = 'CYVR','CYYC';
      do date_utc='21Jan2017'd to '10FEB2017'd;
         arrivals= round(rand('uniform')*25);
         month=Month(date_utc);
         year =year(date_utc);
         output;
      end;
   end;
run;

proc tabulate data=junk;
   class airport year month;
   format airport  $airport.;
   var arrivals;
   table airport='Arrivals at: ',
         year*month,
         arrivals='Arrivals'*sum
   ;
run;

Note that it may very well be that depending on your actual data a single proc tabulate or report could do what you are requesting as those procedures do pretty good jobs of addition based on class (proc tabulate) or group and order (proc report) variables.

 

Tom
Super User Tom
Super User

Depending on how the rest of your process works you probably can simplify your whole process by using BY processing.

Then you could just use PROC TRANSPOSE to convert ariport metadata into the variable and/or label name.  Then you don't need to generate code as PROC TRANSPOSE can generate the name and/or label from data in the table.

So perhpas something along these lines.

proc means data=have ;
  by .... ;
  where code in ("CYVR" "CYYC");
  var xxx;
  output out=tall sum=SUM_of__Calculation_sum ;
run;

data for_transpose ;
  set tall;
  length _name_ $32 _label_ $256 ;
  _name_ = cats(code,'_ARR');
  _label_ = catx(' ','Arrivals at',put(code,$airport.));
run;

proc transpose data=for_transpose out=want ;
  by .... ;
  var SUM_of__Calculation_sum
  id =_name_;
  idlabel = _label_;
run;
BCNAV
Quartz | Level 8

Thank you all for your help. It has been fun to play with the solutions. Both the "if" form of labelling and proc format are nice, but I chose to utilize proc format for all Canadian air traffic control towers:

 

proc format;
	/* These are all 41 towers in Canada as at Sept. 2017 */
	value $TOWERS_ICAO
		"CYXX" = "Abbotsford"
		"CZBB" = "Boundary Bay"
		"CYYC" = "Calgary"
		"CYBW" = "Calgary/Springbank"
		"CYRC" = "Chicoutmi/Saint Honoré"
		"CYEG" = "Edmonton"
		"CZVL" = "Edmonton/Villeneuve"
		"CYFC" = "Frederiction"
		"CYMM" = "Fort McMurray"
		"CYQX" = "Gander"
		"CYHZ" = "Halifax"
		"CYHM" = "Hamilton"
		"CYLW" = "Kelowna"
		"CYKF" = "Kitchener-Waterloo"
		"CYLY" = "Langley"
		"CYXU" = "London"
		"CYQM" = "Moncton"
		"CYUL" = "Montréal/Dorval"
		"CYHU" = "Montréal/Saint-Hubert"
		"CYOO" = "Oshawa"
		"CYOW" = "Ottawa"
		"CYPK" = "Pitt Meadows"
		"CYXS" = "Prince George"
		"CYQB" = "Québec"
		"CYQR" = "Regina"
		"CYJN" = "Saint-Jean"
		"CYXE" = "Saskatoon"
		"CYAM" = "Sault Ste. Marie"
		"CYYT" = "St. John's"
		"CYQT" = "Thunder Bay"
		"CYTZ" = "Toronto Island"
		"CYYZ" = "Toronto Pearson"
		"CYKZ" = "Toronto Buttonville"
		"CCXH" = "Vancouver Harbour"
		"CYVR" = "Vancouver and Waterdrome"
		"CYYJ" = "Victoria"
		"CYXY" = "Whitehorse"
		"CYQG" = "Windsor"
		"CYWG" = "Winnipeg"
		"CYAV" = "Winnipeg/St. Andrews"
		"CYZF" = "Yellowknife"
	;
run;

thanks again!

 

ballardw
Super User

Custom formats are very helpful.

You may also find that mutilabel format is useful. In your airport list it might be that at some time you want to summarize based on a service region and have the individual airports reported as well. A multilabel format allows you to assign a region value as well as the individual.

Only a few procedures, Procs Report, Tabulate, Means/Summary can use the multiple nature but once you have them set up they can be very helpful. I use this approach to map service locations to regions and to have multiple age groups such as 15-21 and 15-17 18-21.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2176 views
  • 2 likes
  • 4 in conversation