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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.