Hi everyone. I am trying to transpose a dataset which ofcourse i had a Proc Sort statement to sort accordingly.
Lets have a quick look at my code:
NOTE: i have a loop to loop ALLDATES from 1 to 69 which the first ALLDATES is MARCH2012 all the way to the last ALLDATES which is DEC2017.
proc sort data=ncpdm.ncp_load_re_oldfull out=sorted_load_re_oldfull;
by region zone state_code state business_area_code business_area
supply_zone_code supply_zone sub_supply_zone_code sub_supply_zone
pmu_name substation_name_tnbt functional_location voltage
re_customer_id/*re_ca_no*/ re_customer_name re_type_main re_type_detail
re_depn_factor re_actual_factor re_status re_decl_load_md
re_tgt_complete re_actual_load_md re_release_capacity
pmu ppu ssu_pe geo_longitude geo_latitude
/* %do AA=1 %to 69;*/
/* &&ALLDATES&AA..*/
/* %end;*/
data_dttm;
run;
proc transpose data=sorted_load_re_oldfull out=trans_load_re_oldfull;
by
%do AA=1 %to 69;
&&ALLDATES&AA..
%end;
;
var
region zone state_code state business_area_code business_area
supply_zone_code supply_zone sub_supply_zone_code sub_supply_zone
pmu_name substation_name_tnbt functional_location voltage
re_customer_id/*re_ca_no*/ re_customer_name re_type_main re_type_detail
re_depn_factor re_actual_factor re_status re_decl_load_md
re_tgt_complete re_actual_load_md re_release_capacity
pmu ppu ssu_pe geo_longitude geo_latitude
/* %do AA=1 %to 69;*/
/* &&ALLDATES&AA..*/
/* %end;*/
data_dttm;
run;
As you all can see, the sort columns are exactly the same in transpose VAR section.
However, I am getting this error:
MPRINT(ALFRED): proc transpose data=sorted_load_re_oldfull out=trans_load_re_oldfull;
MPRINT(ALFRED): by MAR2012 APR2012 MAY2012 JUN2012 JUL2012 AUG2012 SEP2012 OCT2012 NOV2012 DEC2012 JAN2013 FEB2013 MAR2013 APR2013 MAY2013 JUN2013 JUL2013 AUG2013 SEP2013 OCT2013 NOV2013 DEC2013 JAN2014 FEB2014 MAR2014 APR2014 MAY2014 JUN2014 JUL2014
AUG2014 SEP2014 OCT2014 NOV2014 DEC2014 JAN2015 FEB2015 MAR2015 APR2015 MAY2015 JUN2015 JUL2015 AUG2015 SEP2015 OCT2015 NOV2015 DEC2015 JAN2016 FEB2016 MAR2016 APR2016 MAY2016 JUN2016 JUL2016 AUG2016 SEP2016 OCT2016 NOV2016 DEC2016 JAN2017 FEB2017 MAR2017
APR2017 MAY2017 JUN2017 JUL2017 AUG2017 SEP2017 NOV2017 DEC2017 ;
MPRINT(ALFRED): var region zone state_code state business_area_code business_area supply_zone_code supply_zone sub_supply_zone_code sub_supply_zone pmu_name substation_name_tnbt functional_location voltage re_customer_id re_customer_name re_type_main
re_type_detail re_depn_factor re_actual_factor re_status re_decl_load_md re_tgt_complete re_actual_load_md re_release_capacity pmu ppu ssu_pe geo_longitude geo_latitude data_dttm;
MPRINT(ALFRED): run;
NOTE: Numeric variables in the input data set will be converted to character in the output data set.
ERROR: Data set WORK.SORTED_LOAD_RE_OLDFULL is not sorted in ascending sequence. The current BY group has JAN2017 = 933461 and the next BY group has JAN2017 = 6741.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 42 observations read from the data set WORK.SORTED_LOAD_RE_OLDFULL.
WARNING: The data set WORK.TRANS_LOAD_RE_OLDFULL may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.TRANS_LOAD_RE_OLDFULL was not replaced because this step was stopped.
Does anyone have any clue on this?
You have 31 variables in the by statement of the proc sort, but obviously 69 in the by of the proc transpose. That won't work.
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.