Adding Multiple Values to a Dataset Using PROC FORMAT

[Zacc]

@Sas Community, 

I do have a dataset with about 594 variables which I am preparing for analyses. I have added variable labels using the right procedure. To add the corresponding values to few variables, I used PROC FORMAT:

proc format; 
	VALUE HNCO1K 1="Very good" 2="Good"; 
	VALUE HTM50L 0="V" 1="G"; 
RUN;

I would like to add the analogous values which I defined, using PROC FORMAT, to each of the 594 variables. Using the data step, I know I could apply the format as such to few variables:

DATA help_formatted2;

    SET help;

    FORMAT Rate HNCO1K. Code HTM50L.;

RUN;

However, I am struggling to find the most efficient way to add the values to all 594 variables in one attempt.

I shall be most grateful for your help. 

[Kurt_Bremser]

Would you need 594 different formats, or is there a rule to assign the two formats repeatedly?

[Zacc]

@ kurt_Bremser, thanks for your response.
Indeed, I would different formats for all 549 variables.

[Patrick]

@Zacc To apply permanent formats use Proc Datasets and not a data step. Formats are defined in the descriptor portion of a SAS file (table) and to change them doesn't require to process the data. Proc Datasets allows you to manipulate the descriptor portion.

Below code demonstrates how this works. 

Given you have so many variables: IF there would be some naming pattern that allows to derive the format name based on variable name (like: varname abc, related format name abc_fmt) then one could generate the proc datasets code - else you will have to type this for all the 549 variables.

proc format;
  value $sex
    'M'='Male'
    'F'='Female'
    ;
  value age
    10-<14 ='Below 14'
    14-high='14 and older'
    ;
run;

data work.class;
  set sashelp.class;
run;

proc datasets lib=work nolist;
  modify class;
    format
      sex $sex.
      age age.
      ;
  run;
quit;

[Kurt_Bremser]

To automate that, you must first set a rule which controls which format shall apply to which variable. This can be logic based upon the names, or a complete list of variable/format pairs in a dataset.

Which of those do you have?

[Zacc]

The names of each variable in the dataset are the same (i.e. "complete list of variable/format pairs") as the specified VALUE names in the PROC FORMAT . 

[Kurt_Bremser · Accepted solution]

Then you can dynamically create the PROC DATASETS code from SASHELP.VTABLE:

data _null_;
set sashelp.vcolumn end=done;
where libname = "LIBRARY" and memname = "DATASETS";
if _n_ = 1 then call execute("
  proc datasets library=LIBRARY nolist;
  modify DATASET;
  format
");
call execute(name !! " " !! trim(name) !! ".");
if done then call execute("
  ;
  quit;
");
run;

or you can store the value pairs for the FORMAT statement in a macro variable:

proc sql noprint;
select name !! trim(name) !! "." into :formats separated by " "
from dictionary.columns
where libname = "LIBRARY" and memname = "DATASET";
quit;

proc datasets library=LIBRARY nolist;
modify DATASET;
format &formats.;
quit;

Replace LIBRARY and DATASET with your library reference and dataset name; in the WHERE conditions, they must be uppercase.

[Zacc]

Thank you so very @ Kurt_Bremser.