How to recode numerical to character variable or match variable values to labels in SAS Studio?

[Keigo]

I have a variable that only takes 1,2,3 and 9 with meaningful labels ('strongly agree','agree', 'disagree', 'strongly disagree' but I don't know which is which). I need to recode it to a binary variable (e.g, 'agree' and 'disagree').

However, the variable is labeled as a numerical variable in SAS so I can not use 'Recode Values' task to complete recoding nor to recode to another num variable because I don't know how to match the value and the label. Thanks for any help. 

[Tom]

Are you saying you have a numeric variable that has a user defined format attached?  But you are confused how the format is defined?

You and find the name of the format from looking at the how the variable is defined in the dataset.  For exampel by using PROC CONTENTS.  Or if you are using SAS/Studio there are point and click methods to see how the variable is defined.

If you know what catalog has the format definition you can use the FMTLIB option of PROC FORMAT to show the definition.  So if the format attached the variable is called AGREE and the formats are stored in the catalog MYLIB.MYFORMATS then you could run this code:

proc format fmtlib lib=mylib.myformats;
  select agree;
run;

If you are having trouble with that you could also just create a new variable that has a copy of the values and then use PROC FREQ to see what values are being decoded to the strings you see.  For example if your dataset is named HAVE and your variable is named AGREE you could run this code:

data test;
  set have;
  agree2=agree;
run;
proc freq data=test;
  tables agree2*agree / missing list;
run;

But note that will only show you the decodes for the values that happen the exist in HAVE.  Not all of the value range decode mappings that exist in the format mapping.  And since the four values you listed do NOT look like they should map the four strings you listed most likely one of the possibly answers probably does actually appear in your data. 1,2,3,4 would make more sense than 1,2,3,9.  Especially since you have using SPSS terminology ('value labels')  and SPSS is infamous for using valid number to code missing values the 9 is probably a missing or invalid response instead of one of the four strings you listed.

[Keigo]

Hi Tom,

Thank you. Yes, the variable has a user defined format and I can see it from proc contents. However, I don't know the catalog.

I tried the second method you mentioned but it only provide a table with only 1,2,3,4,9

[Keigo]

[Tom]

There are two reasons why the values are not being decoded.

1) The format is NOT attached to the variable.

2) You have not told SAS where to find the format.

If the latter the SAS log should say that it could not find the format.

Or course it is also possible you just have the values and no one every created a format. In that case just look at the documentation.

[Tom]

Do you see the decodes when you print or analyze the values?  Or do you just see the codes?

If you see the decodes then the format must exist in one of the catalogs you have told SAS to look at in the FMTSEARCH system option.  You can use the GETOPTION() function to see the current setting (or use PROC OPTIONS).

%put %sysfunc(getoption(fmtsearch));

[Tom]

Where did you get the data?  How did you get it into a SAS dataset? What documentation did it come with?

[Keigo]

Hi Tom,
Thank you for the help. I got the data from peers and I believe it is from online. I don't have any other file with this SAS data. I can see the decodes so I used
%put %sysfunc(getoption(fmtsearch));
And it returns ( APFMTLIB WORK LIBRARY ) but I don't know where to go from there. Thanks.

[Tom]

The best thing is to ask your peers.

If you want to explore try seeing if there are any formats in those places that have the word AGREE in the decodes.

Since you have three librefs in the search path it should be searching for the format catalog named FORMATS in each of those three librefs, if such format catalogs exists.

Here is a quick way to see if you have any user defined formats in those three catalogs. You can reference the metadata view SASHELP.VFORMAT.   That will have the LIBNAME and MEMNAME variables populated for user defined formats.

proc freq sashelp.vformat; tables libname*memname*objname/ list; run;

That will only get you the names of the formats, but hopefully there is one with a name like AGREE or Q3B.

You can then use PROC FORMAT to look at the definitions of any formats.

So for example if the PROC FREQ shows that APFMTLIB.FORMATS has some user defined formats you can use this code to get a dataset that has those formats' definitions.

proc format lib=APFMTLIB noprint cntlout=formats;run;

You could then search that for any format that has a decode of that contains AGREE.

proc print data=formats ;
  where index(upcase(label),'AGREE');
  var fmtname start label;
run;

[Keigo]

Thanks Tom, I have tried but no FORMATS in any of the three librefs. I guess I will have to find where the data is from.