topic Re: how can I turn a numeric variable to a string; turning 1 value to the name of the variable itsel in New SAS User

how can I turn a numeric variable to a string; turning 1 value to the name of the variable itself

HijB — Fri, 18 Aug 2023 13:40:33 GMT

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

Re: how can I turn a numeric variable to a string; turning 1 value to the name of the variable itsel

Tom — Fri, 18 Aug 2023 13:46:08 GMT

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;

Re: how can I turn a numeric variable to a string; turning 1 value to the name of the variable itsel

Kurt_Bremser — Fri, 18 Aug 2023 14:10:15 GMT

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;

Re: how can I turn a numeric variable to a string; turning 1 value to the name of the variable itsel

ballardw — Fri, 18 Aug 2023 15:29:22 GMT

@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 .

Re: how can I turn a numeric variable to a string; turning 1 value to the name of the variable itsel

Tom — Fri, 18 Aug 2023 18:11:47 GMT

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

Re: how can I turn a numeric variable to a string; turning 1 value to the name of the variable itsel

Patrick — Sun, 20 Aug 2023 03:21:12 GMT

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.