Is there any quick way for below logic? transpose variables into columns with their values.

[chinna0369]

Hi,

I have data like below and I would like to convert variables as test with their result. I have created macro and calling a macro for every variable each time. But the problem is I have more than 4,00,000 records with 100 variables which means 100 tests, so it is taking lot of time for each test and when I am stacking them then I am getting low space pop up notification, and I am unable to run my program completely. Is there any solution for this? 

data I have:

data have;
input Obs $1-2 id $3-7 DT $8-17 var6_d0 $18-20 var20 $21-23  var30 $24-26 var40 $27-29 var70 $30-32 var6_d1 $33-35 var21 $36-38 var31 $39-41 var41 $42-44 var71 $45-47;
datalines;
1 1001 23MAY2023 No                           
2 1001 23MAY2023           No                 
3 1001 23MAY2023                             
4 1001 23MAY2023                             
1 1001 23MAY2023                             
2 1001 23MAY2023                             
3 1001 23MAY2023                             
4 1001 23MAY2023                             
1 1001 23MAY2023                             
2 1001 23MAY2023                             
3 1001 23MAY2023                             
4 1001 23MAY2023                             
1 1001 23MAY2023                             
2 1001 23MAY2023                             
3 1001 23MAY2023                             
4 1001 23MAY2023                             
1 1001 23MAY2023                             
2 1001 23MAY2023                             
3 1001 23MAY2023                             
4 1001 23MAY2023                             
1 1001 20JUN2023 No                           
2 1001 20JUN2023           No                 
3 1001 20JUN2023                             
4 1001 20JUN2023                             
1 1001 20JUN2023                             
2 1001 20JUN2023                             
3 1001 20JUN2023                             
4 1001 20JUN2023                             
1 1001 20JUN2023                             
2 1001 20JUN2023                             
3 1001 20JUN2023                             
4 1001 20JUN2023                             
1 1001 20JUN2023                             
2 1001 20JUN2023                             
3 1001 20JUN2023                             
4 1001 20JUN2023                             
1 1001 20JUN2023                             
2 1001 20JUN2023                             
3 1001 20JUN2023                             
4 1001 20JUN2023                             
1 1001 15AUG2023 No                           
2 1001 15AUG2023           No                 
3 1001 15AUG2023                             
4 1001 15AUG2023                             
1 1001 15AUG2023                             
2 1001 15AUG2023                             
3 1001 15AUG2023                             
4 1001 15AUG2023                             
1 1001 15AUG2023                             
2 1001 15AUG2023                             
;
run;

Macro I have created:

%macro convert_variables_columns(test=);
data want;
	set have;
	where &test. ne ""; 
	test=&test.;
	res=&test.;
run;
proc sort; by id; run;
%mend;

%convert_variables_columns(test=var6_d0);
%convert_variables_columns(test=var20);
%convert_variables_columns(test=var30);
%convert_variables_columns(test=var40);
%convert_variables_columns(test=var70);
%convert_variables_columns(test=var6_d1);
%convert_variables_columns(test=var21);
%convert_variables_columns(test=var31);
%convert_variables_columns(test=var41);
%convert_variables_columns(test=var71);

Data I want:

data want;
input obs $1-2 id $3-7 dt $8-17 test $18-25 res $26-28;
datalines;
1 1001 23MAY2023 var6_d0 No
2 1001 23MAY2023 var40   No
1 1001 20JUN2023 var6_d0 No
2 1001 20JUN2023 var40   No
1 1001 15AUG2023 var6_d0 No
2 1001 15AUG2023 var40   No
;
;
run;

Thanks,

Chi

[Tom · Accepted solution]

Yes.  

But it is very hard to follow what you want.  It would be much easier with a simple dataset that only has 3 or 4 variables and 5 to 10 observations.  Then you could explain exactly how you created the output you wanted from that simple input.

Does your dataset have any combination of variables that form a unique key?  Perhaps OBS, ID an DT ?

If so it looks like you just want a single PROC TRANSPOSE step.

proc transpose data=have 
  out=want(rename=(_name_=test col1=res)
           where=(res ne ' ')  )
;
  by obs id dt notsorted;
  var var6_d0 -- var71;
run;

Result

Obs    Obs     id            DT     test      res

 1      1     1001    23MAY2023    var6_d0    No
 2      2     1001    23MAY2023    var40      No
 3      1     1001    20JUN2023    var6_d0    No
 4      2     1001    20JUN2023    var40      No
 5      1     1001    15AUG2023    var6_d0    No
 6      2     1001    15AUG2023    var40      No

[Patrick]

You could use array processing.

data want(keep=obs id dt varname res);
  set have;
  array test_vars {*} var:;
  length varname $32 res $2;
  do _i=1 to dim(test_vars);
    if test_vars[_i] ne ' ' then
      do;
        varname=vname(test_vars[_i]);
        res=test_vars[_i];
        output;
      end;
  end;
run;

proc print data=want;
run;

[PaigeMiller]

---

@chinna0369 wrote:

I have data like below and I would like to convert variables as test with their result. I have created macro and calling a macro for every variable each time. But the problem is I have more than 4,00,000 records with 100 variables which means 100 tests, so it is taking lot of time for each test and when I am stacking them then I am getting low space pop up notification, and I am unable to run my program completely.

---

Hard to imagine this would take less space after you do the transpose. And when you do the transpose, it will take up twice the disk space, because now you have the original un-transposed data set and the transposed data set.

What are the exact words of this low space notification?

What program are you trying to run when you get the low space notification? What does the program do? What analyses are you trying to perform when you run out of space?

Have you asked your system administrator for more disk space?