PROC TRANSPOSE from Wide to Long

[Ovaty]

Hi guys, good Morning !

I need to do a transpose table only by some variables.

I've try and has no success.

Base table

Material	YYMM	OD	ID	ORD_1	ORD_2	...	ORD_23
110411	201808	533425	10	BA00020902	AC15870200
110411	201808	533425	10	BA00020906	AC15870600
110411	201808	533425	10	BA00020915	AC15894100
110411	201901	539757	20	BA00023631	AC16139200
110411	201901	539757	20	BA00023638	AC16139900
110411	201901	539757	20	BA00023679	AC16193100
110411	201901	539757	20	BA00023682	AC16193400

Want table

Material	YYMM	OD	ID	ORD
110411	201808	533425	10	AC15870200
110411	201808	533425	10	AC15870600
110411	201808	533425	10	AC15894100
110411	201808	533425	10	BA00020902
110411	201808	533425	10	BA00020906
110411	201808	533425	10	BA00020915
110411	201901	539757	20	AC16139200
110411	201901	539757	20	AC16139900
110411	201901	539757	20	AC16193100
110411	201901	539757	20	AC16193400
110411	201901	539757	20	BA00023631
110411	201901	539757	20	BA00023638
110411	201901	539757	20	BA00023679
110411	201901	539757	20	BA00023682

Thanks a lot!

Gustavo

[PeterClemmensen]

So you want to keep data only for ORD10 and ORD20, correct?

[Kurt_Bremser]

Try this

proc transpose
  data=have
  out=want (drop=_name_ rename=(col1=ord))
;
by Material YYMM OD ID;
var ord_1 ord_2;
run;

[Ovaty]

Hi Kurt, thanks for your fast response, but I've no success yet!

Look the result:

Material	YYMM	OD	ID	ord	COL2	COL3	COL4
110411	201808	533425	10	BA00020902	BA00020906	BA00020915
110411	201808	533425	10	AC15870200	AC15870600	AC15894100
110411	201901	539757	20	BA00023631	BA00023638	BA00023679	BA00023682
110411	201901	539757	20	AC16139200	AC16139900	AC16193100	AC16193400

[Kurt_Bremser]

Big hint: supplying example data in usable form (see the following code) is EXTREMELY helpful, as it gives us something to test against.

Without data in readily usable form (data step with datalines), you'll get untested "shots from the hip" that might work as expected.

data have;
input Material $ YYMM :$6. OD :$6. ID :2. ORD_1 :$10. ORD_2 :$10.;
datalines;
110411 201808 533425 10 BA00020902 AC15870200
110411 201808 533425 10 BA00020906 AC15870600
110411 201808 533425 10 BA00020915 AC15894100
110411 201901 539757 20 BA00023631 AC16139200
110411 201901 539757 20 BA00023638 AC16139900
110411 201901 539757 20 BA00023679 AC16193100
110411 201901 539757 20 BA00023682 AC16193400
;

data int1;
set have;
index = _N_;
run;

proc transpose
  data=int1
  out=want (drop=index _name_ rename=(col1=ord))
;
by index Material YYMM OD ID;
var ord_1 ord_2;
run;

proc sort data=want;
by Material YYMM OD ID ord;
run;

proc print data=want noobs;
run;

Result:

Material     YYMM       OD      ID       ord

 110411     201808    533425    10    AC15870200
 110411     201808    533425    10    AC15870600
 110411     201808    533425    10    AC15894100
 110411     201808    533425    10    BA00020902
 110411     201808    533425    10    BA00020906
 110411     201808    533425    10    BA00020915
 110411     201901    539757    20    AC16139200
 110411     201901    539757    20    AC16139900
 110411     201901    539757    20    AC16193100
 110411     201901    539757    20    AC16193400
 110411     201901    539757    20    BA00023631
 110411     201901    539757    20    BA00023638
 110411     201901    539757    20    BA00023679
 110411     201901    539757    20    BA00023682

matches your initial requirement.