BookmarkSubscribeRSS Feed
☑ This topic is solved. 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-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

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