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
@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;
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.