How to convert specific coded categorical data to numerical data

[ChuksManuel]

Hello guys,

I am trying to standardize food questionaire scores in such a way that every level or category corresponds to number of times/ day in my data set. For example, i want number 0 in my dataset to be converted to 0, 1 to 1/30, 2 to 1/15, 3 to 1/7 etc.

I tried to write a SAS micro for it. Here's my code

%macro convert_var(var_in,var_out);

if &var_in. = 0 then &var_out. = 0;

if &var_in. = 1 then &var_out. = 1/30;

if &var_in. = 2 then &var_out. = 2/30;

if &var_in. = 3 then &var_out. = 1/7;

if &var_in. = 4 then &var_out. = 3/7;

if &var_in. = 5 then &var_out. = 6/7;

if &var_in. = 6 then &var_out. = 1;

if &var_in. = 7 then &var_out. = 2.5;

if &var_in. = 8 then &var_out. = 4.5;

if &var_in. = 9 then &var_out. = 7;

%mend;

data work.fooddata;

%convert_var(T1,i_T1)

%convert_var(T2,i_T2)

%convert_var(T3,i_T3)

%convert_var(T4,i_T4)

run;

I ran this code for the first 4 variables that i wanted to convert and it gave me this as my dataset. Note the intial content (before i ran the code) and final content of my dataset(after i ran the code). 

I will be glad to get help on how to modify my code....or can this be done with a do loop? Please any help will be appreciated!

[RW9]

The answer is yes, a do loop would be better, something like (not tested as no test data in the form of a datastep):

data want;
  set have;
  array vals{10} t1--vc2;
  array res{10} 8.;
  do i=1 to 10;
    select(vals{i});
      when(1) res=0;
      when(2) res=1/30;
      ...
      otherwise;
    end;
  end;
run;

Or use a format applied to the variables, that would be simpler:

proc format;
  val tmp 
    1=0
    2=0.0333
    ...;
run;

data want;
  set have;
  format t1 t2 ... vc2 tmp.;
run;

And even better than that would be to normalise your data, so have data going down rather than across, e.g.:

visitdata  paramcd aval
42291      T1      2
42291      T2      0

In this way you only have one variable to alter - and of course you would be following industry guidelines (CDISC) in your data structure.

[PeterClemmensen]

How about a picture format?

proc format library=work;
   picture MyFmt (default=8)
      0 = '0'
      1 = '1/30'
      2 = '2/30'
      3 = '1/7'
      4 = '3/7'
      5 = '6/7'
      6 = '1'
      7 = '2.5'
      8 = '4.5'
      9 = '7'
      ;
run;

data Somedata;
   do x=1 to 9;
      output;
   end;

   format x MyFmt.;
run;

[Kurt_Bremser]

Start with Maxim 2: Read the Log. If that does not give you a clue, post the log here, using the {i} button to preserve it's contents and formatting.

Also, provide your example data in usable form. That means a data step with datalines, posted into a window opened with the "little running man" icon. My footnotes provide links to useful resources, especially a macro that can convert existing datasets into such code, so we can recreate your dataset with ALL atttributes as is by simply copy/pasting the code and submitting it.

[ChuksManuel]

None of the solutions worked. Thank you for trying guys i will keep working on it.

[RW9]

This doesn't tell us anything.  "Not worked" could mean anything, didn't run, didn't do what you expected, there was a power cut, aliens invaded???  We can only advise on the information provided here.

[ChuksManuel]

Thank you. It did not run.

[PaigeMiller]

So, in the future, we want log files pasted into the window that appears after you click on the {i} icon. DO NOT SKIP THIS STEP.

Before your PROC FORMAT, it says The SAS System stopped processing this step because of errors. So we need the LOG before the PROC FORMAT you show, please paste it into the window that appears after you click on the {i} icon. DO NOT SKIP THIS STEP.

[Kurt_Bremser]

PLEASE USE THE {i} BUTTON FOR POSTING LOGS. Copy/paste the text into the subwindow. No need to go through the hassle of making screenshots, posting them, and end up with hard-to-read pics.

SAS looks for a character format, which means that at least one of your variables is of type character.

Clean out your code from the top down, so fix the ERROR in the previous proc format.