topic Re: Wide to Long in SAS Programming

Wide to Long

Ronein — Thu, 23 May 2024 10:12:01 GMT

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

Re: Wide to Long

sbxkoenk — Thu, 23 May 2024 10:31:51 GMT

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

Re: Wide to Long

PaigeMiller — Thu, 23 May 2024 12:35:11 GMT

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.

Re: Wide to Long

Tom — Thu, 23 May 2024 12:54:26 GMT

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;

Re: Wide to Long

ballardw — Thu, 23 May 2024 13:58:57 GMT

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.

Re: Wide to Long

Ronein — Sun, 26 May 2024 04:39:12 GMT

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;

Re: Wide to Long

sbxkoenk — Sun, 26 May 2024 12:21:21 GMT

See your other topic thread :

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

Koen