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;
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;
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: