topic Re: A Reverse Transpose Issue in SAS Procedures

A Reverse Transpose Issue

need_sas_help — Tue, 07 Oct 2014 20:28:26 GMT

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.

Re: A Reverse Transpose Issue

PGStats — Tue, 07 Oct 2014 21:02:40 GMT

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;

Re: A Reverse Transpose Issue

art297 — Tue, 07 Oct 2014 21:20:48 GMT

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;

Re: A Reverse Transpose Issue

Ksharp — Wed, 08 Oct 2014 13:52:17 GMT

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