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
So you want to keep data only for ORD10 and ORD20, correct?
Try this
proc transpose
data=have
out=want (drop=_name_ rename=(col1=ord))
;
by Material YYMM OD ID;
var ord_1 ord_2;
run;
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 |
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.