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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.