BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gen-E79
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Or use the shortened:

First_not_blank=coalescec(of _char_);

 

Any advances Smiley Happy

View solution in original post

7 REPLIES 7
Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

data_null__
Jade | Level 19

@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;

Capture.PNG 

Astounding
PROC Star

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_);

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Or use the shortened:

First_not_blank=coalescec(of _char_);

 

Any advances Smiley Happy

data_null__
Jade | Level 19

@RW9 wrote:

Or use the shortened:

First_not_blank=coalescec(of _char_);

 

Any advances Smiley Happy


 

 

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[*]);
Gen-E79
Calcite | Level 5

Appreciate the help and the quick response time guys, thats just straight up impressive! 🙂

 

Cheers!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1283 views
  • 5 likes
  • 4 in conversation