topic Re: format of 2 values in SAS Programming

format of 2 values

Ronein — Sun, 10 Jan 2021 05:55:42 GMT

Hello

I have a raw data set with information about 3 variables x,w,z for each customer .

I have another data set called formats_tbl that give information about the label of each variable category value.

As I know proc format give to each value a label....but here I need to do it for each value of varaible/category

Question 1:

I want to add 3 new variables called x_new,w_new,z_new with values of description of the label .

Question 2:

I want to add format to each category of each variable.

What is the way to do it by using the formats_tbl

Data rawwtbl;
input id x w z;
1 1 2 1
2 2 1 3
3 3 3 2
4 1 2 3
5 2 2 1
;
run;

Data formats_tbl;
infile cards truncover;
input var $1. category desc $9.;
cards;
x 1 bad
x 2 good
x 3 verygood
w 1 light
w 2 dark
w 3 verydark
z 1 tall
z 2 medium
z 3 small
;
run;

Re: format of 2 values

Shmuel — Sun, 10 Jan 2021 11:29:55 GMT

Next code is tested:

Data rawtbl;
input id x w z;
cards;
1 1 2 1
2 2 1 3
3 3 3 2
4 1 2 3
5 2 2 1
;
run;

Data formats_tbl;
infile cards truncover;
input var $1. category desc $9.;
cards;
x 1 bad
x 2 good
x 3 verygood
w 1 light
w 2 dark
w 3 verydark
z 1 tall
z 2 medium
z 3 small
;
run;
proc sort data=formats_tbl; by var; run;
data cntl;
 set formats_tbl;
   by var;
      if var = 'x' then fmtname = 'xfmt'; else
      if var = 'w' then fmtname = 'wfmt'; else
      if var = 'z' then fmtname = 'zfmt'; 
	  start = category;
	  rename desc=label;
run;
proc format lib=work cntlin=cntl; run;

data want;
 set rawwtbl;
     format x_new w_new z_new $10.;
	 x_new = put(x,xfmt.);
	 w_new = put(w,wfmt.);
	 z_new = put(z,zfmt.);
run;

Re: format of 2 values

Tom — Sun, 10 Jan 2021 16:54:58 GMT

If the names of the variables are valid to use a names of formats you could do it using something like this:

proc format cntlin=formats_tbl(rename=(var=fmtname category=start desc=label));
run;

data want ;
  set rawwtbl;
  array vars x w z ;
  array new  $9 x_new w_new z_new ;
  do index=1 to dim(vars);
    new[index] = putn(vars[index],vname(vars[index]));
  end;
  drop index;
run;

But unfortunately the format Z already exists. So you might want to do a little more work to make sure the format name created didn't conflict with existing formats. For example appending F to the end of the name.

data formats ;
  set formats_tbl ;
  length fmtname $32 ;
  fmtname=cats(var,'F');
  rename category=start desc=label ;
run;
proc format cntlin=formats;run;

data want ;
  set rawwtbl;
  array vars x w z ;
  array new  $9 x_new w_new z_new ;
  do index=1 to dim(vars);
    new[index] = putn(vars[index],cats(vname(vars[index]),'F.'));
  end;
  drop index;
run;

Re: format of 2 values

Tom — Sun, 10 Jan 2021 17:03:00 GMT

If you transpose the original table you can join the result back to the formats table to find the description. You can then transpose it back.

You can even pull the list of VAR values from the formats table to know which variables to transpose.

proc sql noprint;
  select distinct var into :varlist separated by ' ' 
  from formats_tbl
  ;
quit;

proc transpose data=rawwtbl out=tall;
  by id ;
  var &varlist ;
run;
proc sql noprint;
create table tall2 as
  select a.id,a._name_,b.desc
  from tall a left join formats_tbl b
  on upcase(a._name_) = upcase(b.var)
  and a.col1 = b.category
  order by id
;
quit;
proc transpose data=tall2 out=wide(drop=_name_) suffix=_new;
  by id;
  id _name_;
  var desc ;
run;

data want;
  merge rawwtbl wide ;
  by id;
run;

proc print;
run;

Obs    id    x    w    z    z_new     w_new       x_new

 1      1    1    2    1    tall      dark        bad
 2      2    2    1    3    small     light       good
 3      3    3    3    2    medium    verydark    verygood
 4      4    1    2    3    small     dark        bad
 5      5    2    2    1    tall      dark        good