BookmarkSubscribeRSS Feed
alexkim
Calcite | Level 5

ROW  VARIABLE

1         AGE

2         SEX

3         HEIGHT

 

I would like to combine the above multiple rows variable into one new variable

ROW   VARIABLENEW

1          AGE=_AGE    SEX=_SEX    HEIGHT=_HEIGHT

2 REPLIES 2
andreas_lds
Jade | Level 19

@alexkim wrote:

ROW  VARIABLE

1         AGE

2         SEX

3         HEIGHT

 

I would like to combine the above multiple rows variable into one new variable

ROW   VARIABLENEW

1          AGE=_AGE    SEX=_SEX    HEIGHT=_HEIGHT


Why?

Problem 1: the length maximum length of variablenew depends on the number of variables: for each variable max 32 chars + 1 for the equals sign. For alphanumeric variables can use the length of the variable. For numerics it is a bit more challenging if no format is assigned to the variable, the using 20 seems to be appropriate.

data work.class;
   set sashelp.class;
   format Weight commax10.2 Height 5.1;
run;

data _null_;
   set sashelp.vcolumn(where= (Libname = 'WORK' and Memname = 'CLASS')) end=jobDone;
   
   length varList $ 10000;
   retain maxLength 0 regEx varList;
   
   if _n_ = 1 then do;
      regEx = prxparse('/(\d+)\D?/');
   end;
   
   varList = catx(',', VarList, quote(trim(Name)));
   
   if Type = 'char' then do;
      maxLength = maxLength + Length;
   end;
   else do;
      if missing(Format) or not prxmatch(regEx, trim(Format)) then do;
         maxLength = maxLength + 20;
      end;
      else do;
         calcLength = input(prxposn(regEx, 1, Format), best.);
         put name= calcLength= Format=;
         maxLength = maxLength + calcLength;
      end;
   end;
      
   if jobDone then do;
      /* add space for equals sign and blanks */
      maxLength = maxLength + 2 * countw(varList);
      call symputx('maxLength', maxLength);
      call symputx('varList', varList);
   end;
run;

data want;
   set work.class;
   
   length row 8 variablenew $ &maxLength. _variable $ 32;
   
   row = _n_;
   
   do _variable = &varList.;
      variablenew = catx(' ', variablenew, catx('=', _variable, vvaluex(_variable)));
   end;
   
   keep row variablenew;
run;
jimbarbour
Meteorite | Level 14

@alexkim,

 

I think I would echo @andreas_lds sentiments:  This doesn't sound like the best idea.  I'll show you how to do it, but I'm not sure that it's a good way to do things.

 

OK, first let's get some test data.  If this test data isn't right, then correct it and post it in a reply.  Please remember to use the following buttons in your reply window for code and logs:

jimbarbour_0-1602575113221.png

 

Test data:

 

DATA	Person_Data;
	INPUT	Variable	:	$25.
			Value		$
			;

datalines;
Person_ID 	1
AGE 25
SEX M
HEIGHT 72
Person_ID 	2
AGE 8
SEX F
HEIGHT 46
Person_ID 	3
AGE 67
SEX M
HEIGHT 68
;
RUN;

 

 

OK, so here's code to do what you want:  

 

DATA	Concatenated_Person;
	DROP	Variable
			Value;
	DROP	Person_ID
			Age
			Sex
			Height;

	SET	Person_Data;

	RETAIN	Person_ID
			Age
			Sex
			Height
			;

	IF	UPCASE(Variable)		=	'PERSON_ID'	THEN
		DO;
			Person_Id			=	Value;
			DELETE;
		END;
	ELSE
	IF	UPCASE(Variable)		=	'AGE'	THEN
		DO;
			Age					=	Value;
			DELETE;
		END;
	ELSE
	IF	UPCASE(Variable)		=	'SEX'	THEN
		DO;
			Sex					=	Value;
			DELETE;
		END;
	ELSE
	IF	UPCASE(Variable)		=	'HEIGHT'	THEN
		DO;
			Height				=	Value;
			Variable_New		=	CATX(' ','Person_ID=',Person_ID, 'Age=', Age, 'Sex=',Sex, 'Height=',Height);
			OUTPUT;
			CALL	MISSING(Person_ID, Age, Sex, Height);
			DELETE;
		END;
RUN;

The above code results in:

 

jimbarbour_1-1602575250271.png

 

I might recommend something more along the lines of a program that produces data like this which I think will be a lot more usable:

jimbarbour_3-1602575371548.png

 

Here's a program to produce data like the above:

DATA	Consolidated_Person;
	DROP	Variable
			Value;
	SET	Person_Data;

	RETAIN	Person_ID
			Age
			Sex
			Height
			;

	IF	UPCASE(Variable)		=	'PERSON_ID'	THEN
		DO;
			Person_Id			=	Value;
			DELETE;
		END;
	ELSE
	IF	UPCASE(Variable)		=	'AGE'	THEN
		DO;
			Age					=	Value;
			DELETE;
		END;
	ELSE
	IF	UPCASE(Variable)		=	'SEX'	THEN
		DO;
			Sex					=	Value;
			DELETE;
		END;
	ELSE
	IF	UPCASE(Variable)		=	'HEIGHT'	THEN
		DO;
			Height				=	Value;
			OUTPUT;
			CALL	MISSING(Person_ID, Age, Sex, Height);
			DELETE;
		END;
RUN;

Jim

 

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 741 views
  • 0 likes
  • 3 in conversation