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: Call for Content

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!

Submit your idea!

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
  • 2 replies
  • 720 views
  • 0 likes
  • 3 in conversation