I have a dataset with 250 binary variables:
is there a way replace any value of 1 to the name of the variable itself ,so the data eventually looks like the table below, without having to do each variable individually?
PS: I want to keep both the original numeric variables and the new string variables
data health;
input ID safe house food;
cards;
1 1 . .
2 . 1 1
3 . 1 .
4 1 . 1
;
run;
outcome table:
ID | safe | house | food |
1 | safe | ||
2 | house | food | |
3 | house | ||
4 | safe | food |
You obviously cannot put "FOOD" into a numeric variable. So you will need to make new character variables.
data health;
input ID safe house food;
cards;
1 1 . .
2 . 1 1
3 . 1 .
4 1 . 1
;
data want;
set health;
array nums safe house food;
array names[3] $32 ;
do index=1 to 3;
if nums[index] then names[index]=vname(nums[index]);
end;
drop index;
run;
Use the opportunity to get a long layout, which is better for future analysis:
data want;
set have;
array vars {*} safe--food;
length value $32;
do i = 1 to dim(vars);
if vars{i} ne.
then do;
value = vname(vars{i});
output;
end;
end;
keep id value;
run;
@HijB wrote:
I have a dataset with 250 binary variables:
is there a way replace any value of 1 to the name of the variable itself ,so the data eventually looks like the table below, without having to do each variable individually?
Why? Describe an actual use case for adding 250 variables that basically copy an existing variable.
Here is an example of using your data to create custom formats that will display that text as requested:
data health; input ID safe house food; cards; 1 1 . . 2 . 1 1 3 . 1 . 4 1 . 1 ; run; data use; /* you would only use the list of variables that you want*/ /* the -- is for variables in adjacent columns to create a list*/ set health (keep= safe -- food obs=1); array n (*) _numeric_; length hlo $ 3; do i=1 to dim(n); fmtname=vname(n[i]); start=1; type='N'; label= fmtname; hlo=''; output; start=.; label=' '; hlo='O'; output; end; keep fmtname start type label hlo; run; proc format cntlin=use cntlout=useout; run; proc print data=health; format safe safe. house house. food food.; run;
You can also use the approach in the USE data set to create a text format assignment statement to associate the formats with variables.
Warning: likely will have problems if you have any name literals where you use "this is stupid var"n .
Might be simpler to not try to replicate the same number of variables.
Then you can just use a couple of PROC TRANSPOSE steps.
data health;
input ID safe house food;
cards;
1 1 . .
2 . 1 1
3 . 1 .
4 1 . 1
;
proc transpose data=health out=tall ;
by id;
run;
proc transpose data=tall(where=(col1)) out=wide(drop=_name_ _label_) prefix=issue;
by id;
var _name_;
run;
proc print;
run;
Result
Obs ID issue1 issue2 1 1 safe 2 2 house food 3 3 house 4 4 safe food
Ideally transpose your source data from a wide to a long data structure which is most of the time easier to work with.
There is no need to create a full set of additional string variables if it's just about printing/reporting. You could generate and use formats instead as done in below sample code.
data work.have;
input ID safe house food;
cards;
1 1 . .
2 . 1 1
3 . 1 .
4 1 . 1
;
run;
proc sql noprint;
select
catx(' ','value',name,"1='",name,"';"),
catx(' ',name,cats(name,'.'))
into
:fmt_def separated by ' ',
:fmt_apply separated by' '
from dictionary.columns
where libname='WORK' and memname='HAVE' and upcase(name) ne 'ID'
;
quit;
proc format;
&fmt_def;
run;
/*** proc dataset if you want to apply the formats permanently ***/
/*proc datasets lib=work nolist;*/
/* modify have;*/
/* format &fmt_apply;*/
/* run;*/
/*quit;*/
proc print data=have;
/* format statement here only required if formats not applied permanently */
format &fmt_apply;
run;
Please note that variables Safe, House and Food are still numeric and the internal values stored are still 1 or missing. The generated formats applied just change how the values get printed.
If you apply the formats permanently then you also need to store the compiled format definitions permanently or you need to re-create them whenever you want to use them.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.