@Kurt_Bremser This is a quiet complex situation, I dont know what is going wrong in my code. Can you see it? Also I can see that you changed the datelines and added the Q in it. I cannot do that and I also don't want to change my source data. This is my code: proc sort data=work.filtered_main_dataset;
by type quarter;
run;
data want;
set work.filtered_main_dataset;
where c_phase ne .;
by type;
retain diff_phase;
if first.type then diff_phase = c_phase;
if last.type
then do;
diff_phase = c_phase - diff_phase;
output;
end;
diff_phase = c_phase;
keep type diff_phase;
run;
data long;
set
work.filtered_main_dataset
;
run;
proc transpose
data=long
out=wide (
drop=_name_
where=(type ne "")
)
prefix=q_
;
by type;
var c_phase;
id quarter;
run;
data want_wide;
merge
wide
want
;
by type;
run; This is my log: 2 The SAS System 23:15 Thursday, April 15, 2021
44
45 data long;
46 set
47 work.filtered_main_dataset
48 ;
49 run;
NOTE: There were 7238 observations read from the data set WORK.FILTERED_MAIN_DATASET.
NOTE: The data set WORK.LONG has 7238 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
50
51 proc transpose
52 data=long
53 out=wide (
54 drop=_name_
55 where=(type ne "")
56 )
57 prefix=q_
58 ;
59 by type;
60 var c_phase;
61 id quarter;
62 run;
ERROR: The ID value "q_201904" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B013
ERROR: The ID value "q_201902" occurs twice in the same BY group.
ERROR: The ID value "q_201904" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B014
ERROR: The ID value "q_201904" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B017
ERROR: The ID value "q_201904" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B020
ERROR: The ID value "q_201904" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B022
ERROR: The ID value "q_202004" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B025
ERROR: The ID value "q_202004" occurs twice in the same BY group.
ERROR: The ID value "q_202004" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B028
ERROR: The ID value "q_201904" occurs twice in the same BY group.
ERROR: The ID value "q_202004" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B030
ERROR: The ID value "q_201904" occurs twice in the same BY group.
3 The SAS System 23:15 Thursday, April 15, 2021
NOTE: The above message was for the following BY group:
type=TWK-B035
ERROR: The ID value "q_202001" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
type=TWK-B037
ERROR: The ID value "q_201903" occurs twice in the same BY group.
ERROR: Too many bad BY groups.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 269 observations read from the data set WORK.LONG.
WARNING: The data set WORK.WIDE may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.WIDE was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
63
64 data want_wide;
65 merge
66 wide
67 want
68 ;
69 by type;
70 run;
WARNING: Multiple lengths were specified for the BY variable type by input data sets. This might cause unexpected results.
NOTE: There were 3 observations read from the data set WORK.WIDE.
NOTE: There were 988 observations read from the data set WORK.WANT.
NOTE: The data set WORK.WANT_WIDE has 991 observations and 12 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
71
72
73
74
75 GOPTIONS NOACCESSIBLE;
76 %LET _CLIENTTASKLABEL=;
77 %LET _CLIENTPROCESSFLOWNAME=;
78 %LET _CLIENTPROJECTPATH=;
79 %LET _CLIENTPROJECTPATHHOST=;
80 %LET _CLIENTPROJECTNAME=;
81 %LET _SASPROGRAMFILE=;
82 %LET _SASPROGRAMFILEHOST=;
83
84 ;*';*";*/;quit;run;
85 ODS _ALL_ CLOSE;
86
87
88 QUIT; RUN; This is how the first 28 rows of my filtered_main_dataset looks like:
... View more