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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.