Wide to Long

[Ronein]

Hello

Let's say that I want to create a data set based on sas_help.class data set.

The new data set will have only 2 columns:  Var_name  , Var_Value

What is the way to do it?

I expect that the new data set will have 95 rows (because in sas_help.class have 19 rows with 5 columns so 19*5=95)

thanks

[sbxkoenk · Accepted solution]

data want(drop=Name Sex Age Height Weight i j);
 LENGTH Var_Name $ 32 Var_Value $ 40;
 set sashelp.class;
 array vn{2} $ Name Sex;          /* _CHARACTER_ */
 array vv{3}   Age Height Weight; /* _NUMERIC_   */
 do i=1 to dim(vn);
  Var_Name  = vname(vn(i));
  Var_Value = vn(i);
  output;
 end;
 do j=1 to dim(vv);
  Var_Name  = vname(vv(i));
  Var_Value = trim(left(put(vv(i),best16.)));
  output;
 end;
 run;
/* end of program */

Koen

[PaigeMiller]

Is this really a situation where it is a good idea to convert wide to long? Is this really even a "wide" data set?

I don't see how this is a good idea. Having height and weight and age and sex and name in the same column in the resulting long data set doesn't seem to make sense to me, it's hard to see how this would be more useful for any SAS purpose than the original data set.

[Ronein]

I want to do it only for numeric variables and the target is to calculate the following output:

Can you offer other way to calculate it using the original wide structure  and not convert to long??

proc sql;
create table numeric_Vars as
select libname, memname, name, type, length,
format, informat, label
from dictionary.columns
where libname = 'SASHELP' and memname='CLASS' and upcase(type)='NUM'
;
quit;


proc sql noprint;
select name into : Numeric_Vars_List SEPARATED by ' '
from numeric_Vars
;
quit;
%put &Numeric_Vars_List.; /**Age Height Weight**/


proc sql noprint;
select count(*) as nr_numeric_Vars into :nr_numeric_Vars
from numeric_Vars
;
quit;
%put &nr_numeric_Vars.;


data Long_Structure_Numeric_Vars_Data(DROP=&Numeric_Vars_List. J);
set sashelp.class(KEEP=&Numeric_Vars_List.);
array vv{&nr_numeric_Vars.}   &Numeric_Vars_List.; /* _NUMERIC_   */
do j=1 to dim(vv);
Var_Name  = vname(vv(j));
Var_Value =vv(j);
output;
end;
run;


proc sql;
create table want_summary_Report as
select Var_Name,
       sum(case when Var_Value=. then 1 else 0 end ) as nr_Nulls,
       sum(case when Var_Value=0 then 1 else 0 end ) as nr_0,
       sum(case when Var_Value>0 then 1 else 0 end ) as nr_POS,
       sum(case when Var_Value<0 AND Var_Value=. then 1 else 0 end ) as nr_Neg,
	   count(distinct Var_Value) as nr_distinct_values
from Long_Structure_Numeric_Vars_Data
group by Var_Name
;
quit;

[sbxkoenk]

See your other topic thread :

nr_null nr_zero,nr_POS,nr_NEG for each numeric var - SAS Support Communities

Koen

[Tom]

If the dataset has a key then use PROC TRANSPOSE with a BY statement.

The key to SASHELP.CLASS is NAME.

proc transpose data=sashelp.class
  out=want(drop=name rename=(_name_=var_name col1=var_value))
;
  by name;
  var _all_;
run;

Results:

Obs    var_name     var_value

  1     Name       Alfred
  2     Sex        M
  3     Age                  14
  4     Height               69
  5     Weight            112.5
  6     Name       Alice
  7     Sex        F
  8     Age                  13
  9     Height             56.5
 10     Weight               84
 11     Name       Barbara
 12     Sex        F
 13     Age                  13
 14     Height             65.3
 15     Weight               98
 16     Name       Carol
 17     Sex        F
 18     Age                  14
 19     Height             62.8
 20     Weight            102.5

If the dataset does not have a key then just add one.

data want;
  _key_+1;
  set sashelp.class;
run;

proc transpose data=want
  out=want(drop=_key_ rename=(_name_=var_name col1=var_value) where=(var_name ne '_key_'))
;
  by _key_;
  var _all_;
run;

[ballardw]

If this means you are considering creating a data set with a mix of character and "numeric" values in a single variable then you really need to specify what it would be used for as any "automagic" numeric to character conversion is rife will all sorts of result issues: widths, justification, number of decimal places and such.