DATA Step, Macro, Functions and more

How to permanently format many coded variables efficiently

Reply
Contributor
Posts: 39

How to permanently format many coded variables efficiently

[ Edited ]

Hi all. I would like to permanently replace coded values with their formatted values. The catch is my dataset has 50+ variables that need formatting. I can only think of two ways to do this and both require lots of repetitive coding: 1) use the put function 2) use SQL with put function (at least with SQL I don't have to rename the variable, create a new one, and drop the old one). Does anyone have any other solutions? Perhaps there is a way to use the put function with multiple variables? Or perhaps there is another SAS function that can help? Here is some dummy code to play with:

 

data have;
    input x1 x2 x3;
datalines;
1 1 2
3 2 1
3 3 3
1 2 1
;

proc format;
    value x1f 1='a' 2='b' 3='c';
    value x2f 1='d' 2='e' 3='f';
    value x3f 1='g' 2='h' 3='i';
run;

data wantDS;
    set have(rename=(x1=x1c x2=x2c x3=x3c));
    x1=put(x1c,x1f.);
    x2=put(x2c,x2f.);
    x3=put(x3c,x3f.);
    drop x1c x2c x3c;
run;

proc sql;
create table wantSQL as
select put(x1,x1f.) as x1,
          put(x2,x2f.) as x2,
          put(x3,x3f.) as x3
    from have;
quit;

Super User
Posts: 11,343

Re: How to permanently format many coded variables efficiently

If you have a format then you assign it to the variable. Put will create additional text variables. If you need the format permanently attached then use a format statement in a data step (preferrably the first step involving the variables) or use Proc datasets to assign the format.

Example of the first:

proc format library=work;
    value x1f 1='a' 2='b' 3='c';
    value x2f 1='d' 2='e' 3='f';
    value x3f 1='g' 2='h' 3='i';
run;

data have;
    input x1 x2 x3;
    format x1 x1F. x2 x2f.  x3 x3f.;
datalines;
1 1 2
3 2 1
3 3 3
1 2 1
;
run;

or

 

proc datasets;
   modify  have  ;
      format x1 x1F. x2 x2f.  x3 x3f.;
run;
quit;
Respected Advisor
Posts: 4,920

Re: How to permanently format many coded variables efficiently

You could use code-writing code. Assuming format names derived from variable names, as in your example:

 

filename recode temp;

data _null_;
file recode;
put "data wamt; set have;";
do v = "x1", "x2", "x3";
    w = cats(v, "c");
    f = cats(v, "f.");
    put w "= put(" v "," f "); rename " w "=" v "; drop " v ";";
    end;
put "run;";
run; 

%include recode / source2;

you could also create a table of variable names and formats and have a _null_ step write code from those.

PG
Ask a Question
Discussion stats
  • 2 replies
  • 223 views
  • 1 like
  • 3 in conversation