Hello,
I would like to remove a variable if there is no observation in the reference line of my datasets (in this exemple, it is a='a').
Here is my test data:
data test3;
input a $ var1 var2 var3 var4 var5;
datalines;
a 1 . 2 . 4
b . . 1 2 3
c 1 2 3 4 5
;
I would like an output like that:
a 1 2 4
b . 1 3
c 1 3 5
So I create a macro with the name of all my variables that start by 'var'
proc datasets nolist library = work memtype = data;
contents data = work.test3 out = structure3 noprint;
run;
quit;
data _null_;
set structure3;
retain taeller 0;
if length(name) > 2 then
do;
if substr(lowcase(name),1,3) = "var" then
do;
taeller = taeller + 1;
call symputx('taeller',taeller,'G');
call symputx('vbr' !! trim(left(put(taeller,5.))), trim(left(name)), 'G');
end;
end;
run;
For now, everything is okay. So I modify test3 as I want.
%macro testt2;
%local i;
%do i=1 %to &taeller;
data test3;
set test3;
if 'a'=a then do;
if &&vbr&i=. then drop &&vbr&i;
end;
run;
%end;
%mend testt2;
%testt2;
The problem is in red. SAS does not allow this syntax. If I use do end and set the red words into it all my variables are dropped.
Thanks for your help.
Please try
data test3;
input a $ var1 var2 var3 var4 var5;
datalines;
a 1 . 2 . 4
b . . 1 2 3
c 1 2 3 4 5
;
proc transpose data=test3 out=test4;
by a;
var var1-var5;
run;
proc sql;
select _name_ into :vars separated by ' ' from test4 where col1=. and a='a';
quit;
%put &vars;
data test3;
set test3;
drop &vars;
run;
Thanks,
Jag
will you define 'reference line'?
In my real application, a is a date and 'a' a particular date.
Please try
data test3;
input a $ var1 var2 var3 var4 var5;
datalines;
a 1 . 2 . 4
b . . 1 2 3
c 1 2 3 4 5
;
proc transpose data=test3 out=test4;
by a;
var var1-var5;
run;
proc sql;
select _name_ into :vars separated by ' ' from test4 where col1=. and a='a';
quit;
%put &vars;
data test3;
set test3;
drop &vars;
run;
Thanks,
Jag
Thanks it works well.
I'm not sure this is simpler, but it is (in a way) just one step:
data _null_;
set have;
where a='a';
call execute('data want; set have;');
array vars {*} var1-var5;
do _i_=1 to dim(vars);
if vars{_i_}=. then do;
name = vname(vars{_i_});
call execute ('drop ' || name || ';');
end;
end;
call execute('run;');
stop;
run;
This program assumes there is only one observation meeting the WHERE condition;
Good luck.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.