Need help on TRANSPOSE

[imdickson]

I am trying to transpose a wide data into a long data. I've tried various settings for TRANSPOSE but it seems like my fundamental for TRANSPOSE is rather weak.

Here is my data:

data wide; 
  input ID lt_datetime lp_datetime sp_datetime st_datetime lt_mva lt_mwa lp_mva lp_mwa st_mva st_mwa sp_mva sp_mwa; 
cards; 
1  21JAN2018:00:00:00 17FEB2018:12:30:00 20MAR2018:06:00:00 04MAR2018:04:30:00 11 12 13 14 15 16 17 18 
2  21JAN2017:10:00:00 17FEB2017:03:30:00 20MAR2017:06:30:00 07MAR2017:02:30:00 11 12 13 14 15 16 17 18 
3  21JAN2016:07:00:00 17FEB2016:07:30:00 20MAR2016:08:00:00 09MAR2016:08:00:00 11 12 13 14 15 16 17 18 
; 
run ; 

I want it to look like this:

ID  profile datetime             mva   mwa
1   lt      21JAN2018:00:00:00   11    15
1   lp      17FEB2018:12:30:00   12    16
1   st      20MAR2018:06:00:00   13    17
1   sp      04MAR2018:04:30:00   14    18
2   lt      21JAN2017:10:00:00   11    15
2   lp      17FEB2017:03:30:00   12    16
2   st      20MAR2017:06:30:00   13    17
2   sp      07MAR2017:02:30:00   14    18
3   lt      21JAN2016:07:00:00   11    15
3   lp      17FEB2016:07:30:00   12    16
3   st      20MAR2016:08:00:00   13    17
3   sp      09MAR2016:08:00:00   14    18

However, i cannot think of the proper logic for it. I had this code that only gives me the result transposed for datetime but not for other mva mwa:

proc transpose data =assettnbtjoined out=transposeddt;
var lt_datetime lp_datetime st_datetime sp_datetime;
by newID;
run;

Result of the code:

newID	_NAME_	_LABEL_	COL1
1	lt_datetime	Local Trough Date and Time	15SEP18:08:00:00
1	lp_datetime	Local Peak Date and Time	28SEP18:12:30:00
1	st_datetime	System Trough Date and Time	09SEP18:08:00:00
1	sp_datetime	System Peak Date and Time	25SEP18:14:30:00
2	lt_datetime	Local Trough Date and Time	15SEP18:08:00:00
2	lp_datetime	Local Peak Date and Time	28SEP18:12:30:00
2	st_datetime	System Trough Date and Time	09SEP18:08:00:00
2	sp_datetime	System Peak Date and Time	25SEP18:14:30:00

How do i tweak my code into the result that i want?

[Kurt_Bremser]

Try this in a data step:

data wide; 
input
  ID
  (lt_datetime lp_datetime sp_datetime st_datetime) (:datetime19.)
  lt_mva lt_mwa lp_mva lp_mwa st_mva st_mwa sp_mva sp_mwa
;
cards; 
1  21JAN2018:00:00:00 17FEB2018:12:30:00 20MAR2018:06:00:00 04MAR2018:04:30:00 11 12 13 14 15 16 17 18 
2  21JAN2017:10:00:00 17FEB2017:03:30:00 20MAR2017:06:30:00 07MAR2017:02:30:00 11 12 13 14 15 16 17 18 
3  21JAN2016:07:00:00 17FEB2016:07:30:00 20MAR2016:08:00:00 09MAR2016:08:00:00 11 12 13 14 15 16 17 18 
; 
run;

data want (
  keep=id profile datetime mva mwa
);
set wide;
array dt{4} lt_datetime lp_datetime sp_datetime st_datetime;
array mv{4} lt_mva lp_mva st_mva sp_mva;
array mw{4} lt_mwa lp_mwa st_mwa sp_mwa;
array prof{4} $ _temporary_ ('lt','lp','sp','st');
do i = 1 to 4;
  profile = prof{i};
  datetime = dt{i};
  mva = mv{i};
  mwa = mw{i};
  output;
end;
format datetime datetime19.;
run;

I corrected your example data step (informat for datetime values).

Note that the order of values in your example data does not match with your expected output.

[Ksharp]

I shameless take sas dataset from KurtBremser 

data wide; 
input
  ID
  (lt_datetime lp_datetime sp_datetime st_datetime) (:datetime19.)
  lt_mva lt_mwa lp_mva lp_mwa st_mva st_mwa sp_mva sp_mwa
;
format lt_datetime lp_datetime sp_datetime st_datetime datetime19.;
cards; 
1  21JAN2018:00:00:00 17FEB2018:12:30:00 20MAR2018:06:00:00 04MAR2018:04:30:00 11 12 13 14 15 16 17 18 
2  21JAN2017:10:00:00 17FEB2017:03:30:00 20MAR2017:06:30:00 07MAR2017:02:30:00 11 12 13 14 15 16 17 18 
3  21JAN2016:07:00:00 17FEB2016:07:30:00 20MAR2016:08:00:00 09MAR2016:08:00:00 11 12 13 14 15 16 17 18 
; 
run;
proc transpose data=wide out=temp;
by id;
var lt_datetime lp_datetime sp_datetime st_datetime
  lt_mva lt_mwa lp_mva lp_mwa st_mva st_mwa sp_mva sp_mwa;
run;
data temp;
 set temp;
 id1=scan(_name_,1,'_');
 vname=scan(_name_,-1,'_');
run;
proc sort data=temp;
by id id1;
run;
proc transpose data=temp out=temp1;
by id id1;
id vname;
var col1;
run;
data want;
 set temp1;
 drop _name_;
 format datetime datetime19.;
run;

[data_null__]

data wide; 
   input ID (lt_datetime lp_datetime sp_datetime st_datetime) (:datetime.) lt_mva lt_mwa lp_mva lp_mwa st_mva st_mwa sp_mva sp_mwa; 
   format lt_datetime lp_datetime sp_datetime st_datetime datetime.;
   cards; 
1  21JAN2018:00:00:00 17FEB2018:12:30:00 20MAR2018:06:00:00 04MAR2018:04:30:00 11 12 13 14 15 16 17 18 
2  21JAN2017:10:00:00 17FEB2017:03:30:00 20MAR2017:06:30:00 07MAR2017:02:30:00 11 12 13 14 15 16 17 18 
3  21JAN2016:07:00:00 17FEB2016:07:30:00 20MAR2016:08:00:00 09MAR2016:08:00:00 11 12 13 14 15 16 17 18 
;;;; 
   run;
proc print;
   run; 
proc transpose data=wide out=tall;
   by id;
   run;
data tall(index=(indx1=(id profile)));
   set tall;
   length profile $2;
   profile = scan(_name_,1,'_');
   _name_ = scan(_name_,-1,'_');
   run;
proc transpose data=tall out=want(drop=_name_);
   by id profile;
   var col1;
   format datetime: datetime.;
   run;
proc print;
   run;

[imdickson]

THANKS Everyone. You guys helped me so much! I really appreciate it.

I added a copy statement for my own R&D. I cannot think of the logic to populate the first substation_name_psi to the remaining of the blank within the same newID.

Is there anyone that can provide me a logic statement in a datastep without a dataline?

[data_null__]

Add to BY statement.

[imdickson]

@Ksharp @Kurt_Bremser @data_null__

Thanks everyone for the big help!

I now want to transpose data from long to wide. I've followed some guides online. However, after one round of transpose, most of my columns wont be in the transposed table. I want all my other columns that are not transposed to be remained the in "Transposed" table.

Table below is pending to be transposed.

I then use my code below:

proc sort data=scadajoinedtable;
by newID voltagetype;
run;

proc transpose data=scadajoinedtable out=finalscadajoinedtranstable;
by newID voltagetype;
id _NAME_ ;
var COL1;
run;

And this is what i get:

So What i wanted more(columns) are(without transposing these):

substation_psi

power

voltagecalc

current

Is there a way for me to achieve so by tweaking proc transpose or have another round of transpose/data step/merge?