Have;
X Y
Indicator S
Municipality K
Gender T
Value 0,5
Indicator S
Municipality K
Gender T
Value 3
Indicator L
Municipality K
Gender M
Value 10
Want to have;
Indicator Municipality Gender Value
S K T 0,5
S K T 3
L K M 10
If that is your entire data set this does basically what is requested. Note the use of a DATA STEP to provide example data that can be used with code.
data have; input X :$15. Y :$5.; datalines; Indicator S Municipality K Gender T Value 0,5 Indicator S Municipality K Gender T Value 3 Indicator L Municipality K Gender M Value 10 ; Proc sort data=have; by x ; run; proc transpose data=have out=trans1 (drop=_name_) ; by x; var y; run; proc transpose data=trans1 out=want (drop=_name_); id x; var col: ; run;
If you need that Value variable to be numeric you will need another data step to convert the character value to numeric.
@Hundredaire wrote:
Have;
X Y
Indicator S
Municipality K
Gender T
Value 0,5
Indicator S
Municipality K
Gender T
Value 3
Indicator L
Municipality K
Gender M
Value 10
Want to have;
Indicator Municipality Gender Value
S K T 0,5
S K T 3
L K M 10
You need a third variable to indicate which sets of observations you want to transposed into one.
If they are always in that order then you could use X='Indicator' as the trigger to start a new group.
data step1;
set have;
group + (x = 'Indicator');
run;
Now your data is in a format that can be used with PROC TRANSPOSE.
proc transpose data=step1 out=want ( drop=_name_);
by group;
id x ;
var y;
run;
If the variable "VALUE" is supposed to be numeric you will need to convert it later.
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!
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.