I have a following dataset with character variables (JDE_U, S_Number, SAP_Description, JD_201201, SA_201201, JD_201202, SA_201202)
JDE_U S_Number SAP_Description JD_201201 SA_201201 JD_201202 SA_201202
----- ------------- --------------------- -------------- --------------- -------------- --------------
# 000.123 M3.5*5.8 D0007146 0 1 9 8
2 000.135.0 Slid-Ring 7 9 7 3
9 000.140 Pin 10 9 6 0
I need to tranpose the dataset and the new variable is going to be TYPE. Under the variable TYPE, the subgroup is going to be: JD, SA. Also as an example JD_201201 variable will become _201201.
JDE_U S_Number SAP_Description TYPE _201201 _201202
-------- ------------- --------------------- ------ ----------- -----------
# 000.123 M3.5*5.8 D0007146 JD 0 9
# 000.123 M3.5*5.8 D0007146 SA 1 8
2 000.135.0 Slid-Ring JD 7 7
2 000.135.0 Slid-Ring SA 9 3
9 000.140 Pin JD 10 6
9 000.140 Pin SA 9 0
I know how to code reverse tranpose but in this complex situation it become very confusing for me to code. Working on a dataset with a lot of manupulation restrictions, so I had asked few other question earlier. Sorry. Thanks for helping me from the bottom of my heart.
Requires two transpositions:
data have;
length JDE_U S_Number SAP_Description JD_201201 SA_201201 JD_201202 SA_201202 $20;
input JDE_U S_Number SAP_Description & JD_201201 SA_201201 JD_201202 SA_201202;
datalines;
# 000.123 M3.5*5.8 D0007146 0 1 9 8
2 000.135.0 Slid-Ring 7 9 7 3
9 000.140 Pin 10 9 6 0
;
proc transpose data=have out=temp0;
by JDE_U S_Number SAP_Description notsorted;
var JD_: SA_:;
run;
data temp1;
set temp0;
varname = scan(_NAME_,2,"_");
type = scan(_NAME_,1,"_");
drop _NAME_;
run;
proc sort data=temp1; by JDE_U S_Number SAP_Description type; run;
proc transpose data=temp1 out=want(drop=_NAME_);
by JDE_U S_Number SAP_Description type;
var col1;
id varname;
run;
PG
I think you only need a simple datastep:
data want (drop=JD_: SA_:);
set have;
type='JD';
_201201=JD_201201;
_201202=JD_201202;
output;
type='SA';
_201201=SA_201201;
_201202=SA_201202;
output;
run;
Of course. If you have lots of variables need to be changed :
data have; length JDE_U S_Number SAP_Description JD_201201 SA_201201 JD_201202 SA_201202 $20; input JDE_U S_Number SAP_Description & JD_201201 SA_201201 JD_201202 SA_201202; datalines; # 000.123 M_3.5*5.8 D0007146 0 1 9 8 2 000.135.0 Slid-Ring 7 9 7 3 9 000.140 Pin 10 9 6 0 ; run; proc sql; select cats('_',scan(name,-1,'_'),'=JD_',scan(name,-1,'_')) into : jd separated by ';' from dictionary.columns where libname='WORK' and memname='HAVE' and name like 'JD~_%' escape '~' ; select cats('_',scan(name,-1,'_'),'=SA_',scan(name,-1,'_')) into : sa separated by ';' from dictionary.columns where libname='WORK' and memname='HAVE' and name like 'SA~_%' escape '~' ; quit; data want (drop=JD_: SA_:); set have; type='JD'; &jd ; output; type='SA'; &sa ; output; run;
Xia Keshan
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.