BookmarkSubscribeRSS Feed
need_sas_help
Calcite | Level 5

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.

3 REPLIES 3
PGStats
Opal | Level 21

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

PG
art297
Opal | Level 21

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;

Ksharp
Super User

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1027 views
  • 0 likes
  • 4 in conversation