topic Re: Transpose in SAS Programming

Transpose

ursula — Wed, 14 Feb 2018 19:05:06 GMT

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,

Re: Transpose

novinosrin — Wed, 14 Feb 2018 19:08:18 GMT

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;

Re: Transpose

Reeza — Wed, 14 Feb 2018 19:09:25 GMT

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,

Re: Transpose

ursula — Wed, 14 Feb 2018 19:27:35 GMT

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

Re: Transpose

novinosrin — Wed, 14 Feb 2018 19:28:07 GMT

ok try:

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

Re: Transpose

Reeza — Wed, 14 Feb 2018 19:28:39 GMT

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

Re: Transpose

ursula — Wed, 14 Feb 2018 19:48:14 GMT

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

Re: Transpose

Reeza — Wed, 14 Feb 2018 19:52:16 GMT

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

Re: Transpose

ursula — Wed, 14 Feb 2018 20:14:00 GMT

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.

Re: Transpose

Reeza — Wed, 14 Feb 2018 20:17:40 GMT

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

Re: Transpose

ursula — Wed, 14 Feb 2018 20:19:53 GMT

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

Re: Transpose

ursula — Wed, 14 Feb 2018 20:25:23 GMT

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

I think we do need the var statement.

Thanks for your time and support!

Re: Transpose

ursula — Wed, 14 Feb 2018 20:42:37 GMT

Thanks for your time and support!

I will clean my raw data and rerun it again.

I'm sure it will work!

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

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

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

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

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

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