BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Zacc
Fluorite | Level 6

@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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

7 REPLIES 7
Zacc
Fluorite | Level 6
@ kurt_Bremser, thanks for your response.
Indeed, I would different formats for all 549 variables.
Patrick
Opal | Level 21

@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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6
Thank you so very @ Kurt_Bremser.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

LIBNAME 101

Follow along as SAS technical trainer Dominique Weatherspoon expertly answers all your questions about SAS Libraries.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2074 views
  • 2 likes
  • 3 in conversation