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!
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.