In the first Star Wars movie, Obi-wan uses Jedi mind tricks to convince the stormtroopers that the droids they see are not the droids they're looking for. A colleague at SAS passed along a question from a SAS user where the column labels they were seeing were NOT the labels they were looking for:
Does anybody have code to modify SAS column labels so that the variable name and label are simultaneously displayed when viewing the table? For example, we would like to create a new label that has the value 'VarName: Label for VarName' instead of just 'Label for VarName'.
I started thinking about how I would customize the labels using SAS code. I'd want the process to be as automated as possible, and I'd want to accomplish the task without rewriting all of the data in the table just to change the labels. I settled on using a little PROC SQL with its dictionary table magic, a pinch of SAS macro, and finally the raw power of PROC DATASETS to do the deed. Let's start by creating a dataset to play with:
data haircuts; input Name $ Amt; label name='Person' Amt='Price of Last Haircut'; datalines; Luke 25 Obi-wan 15 Darth 0 ; |
The dataset (with labels displayed) looks like this:
Now, we'll use PROC SQL and the dictionary.columns table to construct the ATTRIB statements we'll deploy in PROC DATASETS:
proc sql noprint; select cat('attrib ', strip(Name), ' Label="',catx(': ',Name,Label),'";') into :code separated by ' ' from dictionary.columns where memname='HAIRCUTS' and LIBNAME='WORK' ; quit; |
The macro variable CODE now holds all of the ATTRIB statements we will need in the subsequent PROC DATASETS step:
attrib Name Label="Name: Person"; attrib Amt Label="Amt: Price of Last Haircut";
Finally, we'll submit the PROC DATASETS code to make the changes to our dataset descriptor:
proc datasets lib=work memtype=data nolist; modify haircuts; &code run; |
And violá! Labels just like the customer ordered:
So now, these are the labels we were looking for and my work here is done (for today, at any rate).
Until next time - may the SAS be with you!
Mark
5 Comments
Shame the old sas system viewer is 'no longer" !
It offered just that option - to display as column header, either name or label or both.
Nice solution.
To add anything seems unneccessary, but to keep the original table attributes without demanding much extra disk space, while still providing a version with the labels extended as you demonstrate, an alternate approach could create sql views for these tables with the improved labels. To avoid introducing new names the views could be created in an alternate library. That allows the original table name to be reused.
I do not know if this is a display thing or not with my browser but the proc datasets section of code is showing as:
proc datasets lib=work memtype=data nolist;
modify haircuts;
&code
run;
I believe it should be as this seems to work correctly:
proc datasets lib=work memtype=data nolist;
modify haircuts;
&code;
run;
Otherwise interesting and great code to use. Thanks...
Thanks for the feedback, William. Yes, that snippet of code should contain only the ampersand character, but for some reason the blog editing software I use often inserts the equivalent HTML '&' even when I use a pre-formatted text block.
I'm glad you got it working, and found the material interesting :-)
May the SAS be with you,
Mark
Before you get flamed into a cinder, that was Episode 4, not Episode 1.
04MAY be with you.
Gordon,
Right you are! In my defense, when I first saw the Star Wars movie, there was no reference to Episode IV - and it was a long time ago, in a theater far away. George Lucas' penchant for revising history with updated releases of these films has confused me on more than one occasion.
Of course I meant to reference the FIRST Star Wars movie (Episode IV), not to be confused with the FOURTH Star Wars movie (Episode I). In the spirit of George Lucas, I have opted to edit the blog post - thus changing my own history!
Thanks a bunch for pointing this out early. May the 4th (episode) be with you.
Mark