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;
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
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
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;
@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?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.