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?
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.
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 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;
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?
@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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.