topic Re: Combine multiple rows into one variable in SAS Programming

Combine multiple rows into one variable

alexkim — Tue, 13 Oct 2020 05:45:47 GMT

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

Re: Combine multiple rows into one variable

andreas_lds — Tue, 13 Oct 2020 07:28:26 GMT

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

Re: Combine multiple rows into one variable

jimbarbour — Tue, 13 Oct 2020 07:50:46 GMT

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

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:

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:

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