DATA Step, Macro, Functions and more

How to make data show labels rather than values (plus an extra credit question!)

Reply
New Contributor
Posts: 3

How to make data show labels rather than values (plus an extra credit question!)

I'm copy/pasting a large dataset into Excel so I can use Tableau to make pretty graphics (boss's desire so no option here).  For categorical ("string" or "abc" in Tableau parlance) variables, Tableau likes to work with the "labels" not the numeric values (unless you do some contorted "aliasing" stuff).  I have some variables in the SAS data that are numeric but I've assigned labels via Proc format.  But they don't show as labels in the data - they still show as just the original numerics.  Again, I want the actual data to show the labels, not numbers, so I can cut/paste it that way.  Is there an easy way to get the SAS data to show (I work in the Enterpise thingee) the labels instead of the original numeric values?  That way I can just cut/paste into Excel.  I suppose I could do a data step and force the same proc format statements through the "attrib" statement in the data step and create a new variable, but was hoping for an easier way since that entails cleaning up that "attrib if/then forest" code for 100's of lines.  (p.s. To show values or value labels is a simple click of the mouse in SPSS.  Why does SAS have to overcomplicate everything?!)

 

Another thing I can't get SAS to do is output (in basic stuff like summary stats (proc means proc freq etc) both the variable values and their labels!  Again, in SPSS that is a simple click in general defaults.  I cannot find out how to do that simple thing in SAS - it wants to either spit out the values or if you throw in a format statement then it spits out the labels....BUT it won't do both values and labels (that I can tell).  Any ideas?

 

Ugh...  I used to be an expert in SAS (I thought) - my dang dissertation was an IML program that ran for hours on end!!  Now 20 years later after using SPSS, I'm being forced to use SAS again .... and guess what?  I'm not liking SAS too much....  The simplest of things get so overcomplicated....frustrating.  Oh well.  Thanks,

Frank

PROC Star
Posts: 7,363

Re: How to make data show labels rather than values (plus an extra credit question!)

[ Edited ]

I don't work with EG, so this might not work, but would submitting the simple line:

options label;

 

work?

 

HTH,

Art, CEO, AnalystFinder.com

 

p.s. I like SAS more than SPSS. Just takes a bit to get used to the differences. Smiley Happy

 

New Contributor
Posts: 3

Re: How to make data show labels rather than values (plus an extra credit question!)

Thanks. I tried it - see below - but to no avail. Still only spits out labels not values and labels. Thanks anyway.
proc freq;
title "xtabs by RESIDENT by foremp1";
tables formemp1*resident / missing nocum nopercent norow nocol;
format formemp1 emptypefmt.;
options label;
run;

PROC Star
Posts: 7,363

Re: How to make data show labels rather than values (plus an extra credit question!)

I'd try it again, but moving the options label statement to precede the proc statement. i.e.,

 

options label;

proc freq;
  title "xtabs by RESIDENT by foremp1";
  tables formemp1*resident / missing nocum nopercent norow nocol;
  format formemp1 emptypefmt.;
run;

Art, CEO, AnalystFinder.com

 

p.s. for me that solved the extra credit question as well

 

New Contributor
Posts: 3

Re: How to make data show labels rather than values (plus an extra credit question!)

Thanks, Art. But it still does not spit out in the Freqs table the value and the value label. Oh well - thanks anyhow.
Regarding p.s. I have always used SAS for the more powerful things that SPSS does not do (e.g. experimental design, iterative proportionate fitting (i.e. "raking") for weighting but my call for those was once every 2-3 weeks. The vast majority of my work in market research dealt with data manipulation of rather small data sets of ~1000 cases and ~200 variables and having used both SPSS and SAS extensively I am absolutely certain using SPSS click and point is faster, more transparent, and more error-free than using SAS code. Certain of it. And that coming from someone who still bemoans point and click altogether (I still dream in Lotus 123 keyboard commands - slash FC etc etc - oh the good ol days....). To me coding out data manipulation (which is 80% of stats (errr analytics) work) when one can just click and point data you're looking at (again if its smallish like my typical data sets) and get it done in maybe 70% of the time and actually be seeing the data the whole time is like being forced to drive a car through a computer/screen thrown between your and the windshield. Direct holding/melding/machinating of the data is like driving unencumbered. Coding it out is asking for trouble and making it more complicated and slower than it has to be (when you understand the power of something like SPSS click and point environment and all the powerful functionalities it has such as merging and aggregation). Then again I like to have the code for documentation - but of course SPSS saves all click and point as code so you have that too. Anyhow, sorry to ramble and rant - I really miss my SPSS....
Super User
Posts: 17,829

Re: How to make data show labels rather than values (plus an extra credit question!)

SAS won't do formatted values and underlying values at the same time. Although you're talking about 'labels' that has a different meaning in SAS - it refers to the variable label, not a value label which is what I think you're referring to. 

 

Unfortunately the way to do this is likely to create a new variable that essentially converts the format. I'm not a huge fan of this because then you lose the dynamic nature unless you structure your program appropriately.

 

So using SAS EG and Query Builder, use a computed column to convert the value to a new variable.

 

PUT(varialbe_name, format_name)

 

Then use this new variable as well in your PROC FREQ. 

 

If you're programming it would look like:

data want;
set have;
foremp1_formatted = put(formemp1, emptypefmt.);
run;

proc freq data=want;
tables formemp1*formemp1_formatted*resident/.....;
run;

PS Everything is easier in the 'language' you know, but having worked with both for a while SAS is significantly more powerful than SPSS though it does require some investment in knowledge.  Sometimes it is more steps to accomplish simple things, but the power of the automation features is almost unparalleled to any of the other languages I've worked with, including R/Python. 

 

Super User
Posts: 17,829

Re: How to make data show labels rather than values (plus an extra credit question!)

Also, Tableau can connect directly to your SAS table if that would help you skip any steps.

Ask a Question
Discussion stats
  • 6 replies
  • 203 views
  • 0 likes
  • 3 in conversation