Transpose

[ursula]

Hi there,

anyone can help?

data raw; 
input ID car D2 D3 D4 D5;  ** I have 100 more D variables in the real data **;
datalines;
101 1 100 300 -1 500
102 2 200 -1 400 600
103 3 -1 200 300 -1
;

run;

I wan:

ID	car	DX
101	1	100
101	1	300
101	1	500
102	2	200
102	2	400
102	2	600
103	3	200
103	3	300

Thanks,

[novinosrin]

data raw; 
input ID car D2 D3 D4 D5;  ** I have 100 more D variables in the real data **;
datalines;
101 1 100 300 -1 500
102 2 200 -1 400 600
103 3 -1 200 300 -1
;

run;

proc transpose data=raw out=want(drop=_name_);
by id car;
var  d:;
run;

[ursula]

Thank you, but I do not want to include the -1.

[novinosrin]

ok try:

proc transpose data=raw out=want(where=(COL1>0) drop=_name_);
by id car;
var  d:;
run;

[ursula]

when I run into my real data, the column "car" are all the same values, and COL1, COL2, what does the COL2 means?

[Reeza]

The code works on the sample data. How does your actual data differ from the sample data and post your code and log. 

[ursula]

I have no Idea, why it has another column "COL2" and all are blank.

proc transpose data=Drug out =Drug_1 (where=(COL1>0) drop=_name_);
by ID VS;
var 😧 ;run;

it looks like only generate for one VS.

[Reeza]

If you look at the code we suggested, there's no VAR statement. 

[ursula]

I tried to run without Var statement, it looks error. 

I think we do need the var statement.

Thanks for your time and support!

[ursula]

I figured it out, because there are some ID has duplicate data. that's why it has COL2.

[ursula]

Thanks for your time and support!

I will clean my raw data and rerun it again.

I'm sure it will work!

[Reeza]

Delete it after the TRANSPOSE, use a WHERE data set option.

[Reeza]

Did you try PROC TRANSPOSE?

proc transpose data=raw out=want;
by id car;
run;

---

@ursula wrote:

Hi there,

anyone can help?

data raw; 
input ID car D2 D3 D4 D5;  ** I have 100 more D variables in the real data **;
datalines;
101 1 100 300 -1 500
102 2 200 -1 400 600
103 3 -1 200 300 -1
;

run;

I wan:

ID	car	DX
101	1	100
101	1	300
101	1	500
102	2	200
102	2	400
102	2	600
103	3	200
103	3	300

Thanks,

---