topic Re: Conditional Labelling in SAS Programming

Conditional Labelling

BCNAV — Tue, 05 Sep 2017 15:50:51 GMT

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?

Re: Conditional Labelling

Tom — Tue, 05 Sep 2017 16:14:58 GMT

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;

Re: Conditional Labelling

BrunoMueller — Tue, 05 Sep 2017 16:18:20 GMT

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.));

Re: Conditional Labelling

ballardw — Tue, 05 Sep 2017 16:28:24 GMT

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.

Re: Conditional Labelling

Tom — Tue, 05 Sep 2017 16:28:43 GMT

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;

Re: Conditional Labelling

BCNAV — Thu, 07 Sep 2017 14:04:42 GMT

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!

Re: Conditional Labelling

ballardw — Thu, 07 Sep 2017 19:24:18 GMT

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.