topic Re: Wide to Long for numeric VARS only in SAS Programming

Wide to Long for numeric VARS only

Ronein — Thu, 23 May 2024 11:26:23 GMT

Hello

With continue to previous post,

I want to create a new data set based on sashelp.class data set .

I want that in new data set will have 2 columns; Var_name ,Var_value.

I want it to be done only for numeric vars.

I want to identify the numeric vars automatically (Because I want to apply this code on different data sets)


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;
set sashelp.class;
array vv{&nr_numeric_Vars.}   &Numeric_Vars_List.; /* _NUMERIC_   */
do j=1 to dim(vv);
Var_Name  = vname(vv(i));
Var_Value = vv(i);
output;
end;
run;

The error in log is:

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
      31:1     Var_Name
NOTE: Variable i is uninitialized.
ERROR: Array subscript out of range at line 31 column 19.
Name=Alfred Sex=M Age=14 Height=69 Weight=112.5 j=1 Var_Name=  i=. Var_Value=  _ERROR_=1 _N_=1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set SASHELP.CLASS.
WARNING: The data set WORK.LONG_STRUCTURE_NUMERIC_VARS_DATA may be incomplete.  When this step was stopped there were 0 
         observations and 9 variables.

Re: Wide to Long for numeric VARS only

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

NOTE: Variable i is uninitialized.

You can't use a variable named I until it has been defined and given a value. You have not given it a value.

Re: Wide to Long for numeric VARS only

Ronein — Thu, 23 May 2024 11:49:18 GMT

Great! solution is fine now


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;

Re: Wide to Long for numeric VARS only

Tom — Thu, 23 May 2024 12:41:05 GMT

Why are you working so hard? No code generation is needed.

data want;
  length var_name $32 var_value 8 ;
  set sashelp.class;
  array _num_ _numeric_;
  do _n_=2 to dim(_num_);
    var_name = vname(_num_[_n_]);
    var_value = _num_[_n_];
    output;
  end;
  keep var_name var_value;
run;

The resulting data is the same:

Observation Summary                                                                                                                 
                                                                                                                                    
Observation      Base  Compare                                                                                                      
                                                                                                                                    
First Obs           1        1                                                                                                      
Last  Obs          57       57                                                                                                      
                                                                                                                                    
Number of Observations in Common: 57.                                                                                               
Total Number of Observations Read from WORK.WANT: 57.                                                                               
Total Number of Observations Read from WORK.LONG_STRUCTURE_NUMERIC_VARS_DATA: 57.                                                   
                                                                                                                                    
Number of Observations with Some Compared Variables Unequal: 0.                                                                     
Number of Observations with All Compared Variables Equal: 57.                                                                       
                                                                                                                                    
NOTE: No unequal values were found. All values compared are exactly equal.

The only difference is that my code makes the VAR_NAME variable shorter since SAS names can only be 32 bytes long.

Re: Wide to Long for numeric VARS only

ballardw — Thu, 23 May 2024 16:44:32 GMT

By default Proc Transpose will use all numeric values as Var variables if a Var statement is not included.

So you might look at:

Proc transpose data=sashelp.class
out=trans (keep=_name_ col1 rename=(_name_=var_name col1=var_value));
by _character_ notsorted;
by _all_ notsorted;
run;

for the example. For a truly lazy way to code.

However if the combination of all the ~~_character_~~ _All_ variables does not uniquely identify a record (i.e. there are duplicates and the data has been sorted (grouped) by them) the output will not include var_values for the duplicated by variable combinations.

CAUTION: If the data set has Date, time and datetime values may be difficult to use with the single format that the resulting var_value column will have.