Proc-Transpose-issue

[Konkordanz]

Hi,

its probably easy, but I just doesnt get the proc-transpose-function. My dataset have:

year	ID	number1	number2
2019	111	45	21
2019	112	8	21
2019	113	4	35
2019	114		48
2019	115	165	21
2019	116	2	30
2019	117	12	15
2020	111	46	25
2020	112	9	26
2020	113	3	34
2020	114		42
2020	115	166	20
2020	116	3	29
2020	117	15	13

So, my have-dataset has actually two blocks of IDs underneath. Every ID occurs twice: 1x for the year 2019 and 1x for 2020.

What I want is pretty simple: I want to get the year as a column:

ID	number1_2019	number2_2019	number1_2020	number2_2020
111	45	21	46	25
112	8	21	9	26
113	4	35	3	34
114		48		42
115	165	21	166	20
116	2	30	3	29
117	12	15	15	13

Thank you for help!

[andreas_lds]

The dataset you want to create looks like a report, having data in variable names is almost always a bad idea making the next steps more difficult. So, why do you need that structure?

Afaik your problem can't be solved by using proc transpose once, but by calling it twice and merging the results:

proc sort data= have out= sorted;
   by Id year;
run;

proc transpose data= sorted out= transposed1(drop= _name_) prefix= number1_;
   by Id;
   var number1;
   id year;
run;

proc transpose data= sorted out= transposed2(drop= _name_) prefix= number2_;
   by Id;
   var number2;
   id year;
run;

data want;
   merge transposed1 transposed2;
   by id;
run;

This should work, code is untested, because data wasn't provided in usable form.

[Ksharp]

data have;
infile cards expandtabs truncover;
input year	ID	number1	number2;
cards;
2019	111	45	21
2019	112	8	21
2019	113	4	35
2019	114	. 	48
2019	115	165	21
2019	116	2	30
2019	117	12	15
2020	111	46	25
2020	112	9	26
2020	113	3	34
2020	114	 .	42
2020	115	166	20
2020	116	3	29
2020	117	15	13
;

proc sql noprint;
select distinct catt('have(where=(year=',year,') rename=(
number1=number1_',year,' number2=number2_',year,'))') into :merge separated by ' '
 from have;
quit;
data want;
 merge &merge.;
 by id;
 drop year;
run;

[Kurt_Bremser]

Don't create a dataset like this, long beats wide (Maxim 19). Do not put data into structure.

A report can be easily done like this:

proc report data=have;
column id (number1 number2),year;
define id /group;
define number1 / analysis;
define number2 / analysis;
define year / "" across;
run;

and it does not need prior sorting.

[Konkordanz]

Thx for helping! After reading that, I will try it without transposing.