Hello!
I have a problem that i think has a general solution. It is like this
-----------------------------------------------------------------------------------------------------------------------------------------------
Problem basis:
dataset have observations with unknown number of variables
all variables are of type text
some of them are empty and which ones are varies from one observation to another
Task:
for all observations, ignore all empty variables and return only the value of first occurrence of variable with text per observation
------------------------------------------------------------------------------------------------------------------------------------------------------------------
New to SAS, I can figure this out with number variables but ran into a dead-end with text-variables.
Anyone here that see an obvious solution? 🙂
Thanks
Or use the shortened:
First_not_blank=coalescec(of _char_);
Any advances
It depends what you mean by "first". If you mean the first variable using the internal order in which SAS is storing the variables, you can use:
data want;
set have;
array charvars {*} _character_;
do firstone=1 to dim(charvars) until (charvars{firstone} > ' ');
end;
if firstone > dim(charvars) then firstone=0; /* just in case there are no missing values */
else name_of_first = vname(charvars{firstone});
run;
If you have your own "order" in mind for searching, you will need to replace _CHARACTER_ with a list of variable names in the proper order.
Added a statement in case you are looking to get the name of the first variable.
As an alternative to @Astounding's great example:
data want; infile datalines dlm="," dsd; input var1 $ varb $ xyz $; first_result=scan(catx(",",of _character_),1,","); datalines; astring,something,yv ,nomiss, ,,abc ; run;
The catx() creates a string with your character variables where present seprated by , so the first of that string is going to be the first non-missing.
Do note that is is a good idea to post some test data, in the form of a datastep, so we can provide code working for your data.
@RW9 the coalesceC function seems the obvious choice.
data want;
infile cards dsd missover;
input (var1 varb xyz) ($);
First_not_blank = coalesceC(of var1-character-xyz);
cards;
astring,something,yv
,nomiss
,,abc
;;;;
run;
proc print;
run;
You could easily be right about what's needed. In that case, you don't really need to know the variable names:
First_not_blank = coalescec(of _character_);
Or use the shortened:
First_not_blank=coalescec(of _char_);
Any advances
@RW9 wrote:
Or use the shortened:
First_not_blank=coalescec(of _char_);
Any advances
Yes, while not an issue for your example, that syntax will included the variable on the left in the list _CHAR_. I would normally define an ARRAY just after SET statement to have the benifit of using _CHAR_ and not including unwanted variables in the coalesceC list.
set ...;
array _c[*] _character_;
first_no_blank = coalesce(of _c[*]);
Appreciate the help and the quick response time guys, thats just straight up impressive! 🙂
Cheers!
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.