BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bncoxuk
Obsidian | Level 7

Hi,

In my SAS file, the table looks like below (show only 3 variables here). The blank space are missing values.

sex age married

    20

    20

    25

    25

    30

    30

            C

            C

            S

            S

            X

            X

Now the task is to add a new column which concatenates all the values across all those character variables, and put the variable name in front of the values. It should be like below.

varname

sex_F

sex_F

sex_M

sex_M

age_20

age_20

age_25

age_25

age_30

age_30

married_C

married_C

married_S

married_S

married_X

married_X

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

The following keeps the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

F

F

M

M

.   20

.   20

.   25

.   25

.   30

.   30

.   .       C

.   .       C

.   .       S

.   .       S

.   .       X

.   .       X

;

proc sql noprint;

  select "if not missing("||name||

         ") then newvar=catx('_','"||

         trim(name)||"',"||trim(name)||")"

    into :names separated by ";"

      from dictionary.columns

        where libname="WORK" and

        memname="HAVE"

  ;

quit;

data want;

  set have;

  &names.;

run;

View solution in original post

6 REPLIES 6
Haikuo
Onyx | Level 15

Try this, it will get your contents, but not the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

F

F

M

M

.   20

.   20

.   25

.   25

.   30

.   30

.   .       C

.   .       C

.   .       S

.   .       S

.   .       X

.   .       X

;

proc transpose data=have out=have1;

   var _all_;

   copy _all_;

   run;

   proc sort data=have1;

   by _name_;

   run;

proc transpose data=have1(where=(not missing(_name_))) out=have2;

   by _name_;

   var col:;

   run;

   data want (keep=new);

   set have2 (where=(not missing(col1)));

   length new $40.;

   new=strip(_name_)||'_'||strip(col1);

   run;

proc print;run;

Regards,

Haikuo

art297
Opal | Level 21

The following keeps the order:

options missing='';

data have ;

infile cards missover;

input sex :$ age married :$;

cards;

F

F

M

M

.   20

.   20

.   25

.   25

.   30

.   30

.   .       C

.   .       C

.   .       S

.   .       S

.   .       X

.   .       X

;

proc sql noprint;

  select "if not missing("||name||

         ") then newvar=catx('_','"||

         trim(name)||"',"||trim(name)||")"

    into :names separated by ";"

      from dictionary.columns

        where libname="WORK" and

        memname="HAVE"

  ;

quit;

data want;

  set have;

  &names.;

run;

bncoxuk
Obsidian | Level 7

Always a 5* quick reply and an answer. Thank you!

rtritz
Calcite | Level 5

Hello, Slightly modifying the input dataset, another possibility is below:

data have ;

infile cards missover;

input sex :$ age married :$;

cat+1;

cards;

F

F

M

M  

.     20

.     20

.     25

.     25

.     30

.     30  

.     .    C

.     .    C

.     .     S

.     .     S

.     .     X

.     .     X

;

proc transpose data = have out=thave;  

by cat;   var sex age married;

run;

data thave2(drop=cat _name_ col1 col2);  

set thave;  

col2 = strip(col1);  

if col1 = ' ' then delete;  

if col2 ne '.' then do;

varname = catx('_',_name_,col2);

output;  

end;

run;

HTH, Rich

Tom
Super User Tom
Super User

Not sure what you want to do when an observation has more than one variable with a non missing value.  This code assumes you want to concatenate those values into the result with a space between them.

You can use VNEXT to loop through the variables in your dataset. You can use vvaluex to get the value based on the variable name.

data have;

  input sex $ age married $ @@;

cards;

F . . F . . M . . M . .

. 20 . .  20 . . 25 . . 25 . . 30 . . 30 .

. . C . . C . . S . . S . . X . . X

run;

data want ;

  set have ;

  length _name_ $32;

  length _value_ $2000 ;

  do while(1);

    call vnext(_name_);

    if upcase(_name_) eq '_NAME_' then leave;

    if strip(vvaluex(_name_)) not in (' ','.') then

      _value_=catx(' ',_value_,catx('_',_name_,vvaluex(_name_)))

    ;

  end;

  keep _value_;

run;

bncoxuk
Obsidian | Level 7

Cool approach.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 1666 views
  • 7 likes
  • 5 in conversation