proc transpose long to wide - multiple columns

[pacman94]

Hello I have the following dataset

ID	followup	age	meddrugs	gender	score
1	3	25	ABC	male	1
1	6	25	ABC	male	2
2	3	31	GHI	female	1
2	6	31	ABC	female	2
2	12	31	DEF	female	1
3	6	45	ABC	female	2
3	12	45	GHI	female	1

I would like to transpose ALL to wide by ID and follow up to something like this:

ID

age_3

age_6

age_12

meddrugs_3

meddrugs_6

meddrugs_12

gender_3

gender_6

gender_12

score_3

score_6

score_12

1

25

25

ABC

ABC

male

male

1

2

2

31

31

31

GHI

ABC

DEF

female

female

female

1

2

1

3

45

45

ABC

GHI

female

female

2

1

I did this following:

proc sort data = test; by ID followup;run;

proc transpose data = test out = transpose;

by ID; id followup;

var age meddrugs gender score;

run;

not getting what i want 😞

[Kurt_Bremser]

You can create such a report easier with PROC REPORT:

proc report data=have;
column id gender followup,(age meddrugs score);
define id / group;
define gender /group;
define followup / "" across;
define age / display;
define meddrugs / display;
define score / analysis;
run;

or do you need the data for some kind of regression analysis?

[pacman94]

This works too! I am looking for more converting into a dataset

[Kurt_Bremser]

The quickest way to write is a double transpose:

proc transpose data=have out=t1;
by id followup;
var gender age meddrugs score;
run;

proc transpose data=t1 out=wide (drop=_name_);
by id;
var col1;
id _name_ followup;
run;

but that will have all variables as character.

To preserve the types, a little more effort is on order:

data wide;
set have;
by id;
array ages{3:12} age3-age12;
array meddrug{3:12} $ meddrug3-meddrug12;
array scores{3:12} score3-score12;
retain ages meddrug scores;
if first.id then
do i = 3 to 12;
  ages{i} = .;
  meddrug{i} = "";
  scores{i} = .;
end;
ages{followup} = age;
meddrug{followup} = meddrugs;
scores{followup} = score;
if last.id;
keep id age3 age6 age12 meddrug3 meddrug6 meddrug12 score3 score6 score12;
run;

[PaigeMiller]

---

@pacman94 wrote:
This works too! I am looking for more converting into a dataset

---

A wide data set is almost always a poor choice in this situation. Whatever programming you will do with this wide data set after you create it will be much more difficult than if you leave the data set as a long data set. If you can explain your next step of analysis of this wide data set, I'm sure we can find easier ways to program that analysis if you leave the data set as long (although there are exceptions).

[novinosrin]

Sir @PaigeMiller  How I wish the C'Bank folks took notice of that "very" important message. Perhaps, they may listen to a senior like you unlike me. Should we swap companies?:) I love the snow and upstate new york 

[Kurt_Bremser]

Point them to the many places where I referred to Maxim 19. I feel I do that at least twice a week.

Have a large sign on your office wall:

LONG BEATS WIDE

[novinosrin]

Haha I fully agree. One of the biggest lessons I learned in life is I lack EQ(Emotional quotient) skills to cope with bureaucracy. I'm afraid my blood pressure is gonna shoot up before I finish typing. lol

Rules :

1. Only SQL no datastep or any other reasonable solution

2. Only hard code +spaghetti with macro abuse

3. If somebody questions, shut him/her up. Tell that employee to be happy with the good pay offered(good pay is true)

4. Make sure,  remain supporting legacy applications until the Bank exists,

5. Screw the company as chances are that top management will never come to know as things can be concealed.

If you have the EQ skill to cope with all mental stress, sure the pay is excellent  i must admit. 

PS To add some spice, those who do that claim to have 20+ years of experience in SAS 🙂

[ballardw]

---

@novinosrin wrote:

Haha I fully agree. One of the biggest lessons I learned in life is I lack EQ(Emotional quotient) skills to cope with bureaucracy. I'm afraid my blood pressure is gonna shoot up before I finish typing. lol

PS To add some spice, those who do that claim to have 20+ years of experience in SAS 🙂

---

Have they noticed that punch-cards went away yet?

( I actually did start programming on punch cards.)

Maybe their SAS experience is like my COBOL experience: One class 30 years ago and never actually used.

[Kurt_Bremser]

---

@ballardw wrote:

---

( I actually did start programming on punch cards.)

---

Me too. CDC 6600, PASCAL.

[ballardw]

---

@Kurt_Bremser wrote:

---

@ballardw wrote:

---

( I actually did start programming on punch cards.)

---

Me too. CDC 6600, PASCAL.

---

IBM 360 Fortran and PL/1 .

[Tom]

You can use PROC SUMMARY to transpose many variables at once. https://support.sas.com/resources/papers/proceedings10/102-2010.pdf

But it won't use the followup value as part of the name however.

data have ;
  input ID followup age meddrugs $ gender $ score;
cards;
1 3 25 ABC male 1
1 6 25 ABC male 2
2 3 31 GHI female 1
2 6 31 ABC female 2
2 12 31 DEF female 1
3 6 45 ABC female 2
3 12 45 GHI female 1
;

proc summary data=have nway ;
 class id;
 output out=want (drop=_type_ _freq_)
        idgroup(out[3] (followup age meddrugs gender score)=)
 ;
run;

To use PROC TRANSPOSE you need to transpose twice. (or more since you have mixed numeric and character variables).

proc transpose data=have out=tall_n;
  by id followup;
  var age score;
run;

proc transpose data=tall_n out=want_n(drop=_name_) delim=_;
  by id ;
  id _name_ followup;
  var col1;
run;

proc transpose data=have out=tall_c;
  by id followup;
  var meddrugs gender;
run;

proc transpose data=tall_c out=want_c(drop=_name_) delim=_;
  by id ;
  id _name_ followup;
  var col1;
run;

data want;
  merge want_n want_c;
  by id;
run;

[Ksharp]

data have ;
  input ID followup age meddrugs $ gender $ score;
cards;
1 3 25 ABC male 1
1 6 25 ABC male 2
2 3 31 GHI female 1
2 6 31 ABC female 2
2 12 31 DEF female 1
3 6 45 ABC female 2
3 12 45 GHI female 1
;
proc sql noprint;
select distinct catt('have(where=(followup=',followup,') 
 rename=(age=age',followup,' meddrugs=meddrugs',followup,' 
 gender=gender',followup,' score=score',followup,'))')
	into : merge separated by ' '
 from have
  order by followup ;
quit;
data want;
 merge &merge;
 by id;
 drop followup;
run;

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf