I am trying to solve for an error that I am getting from proc transpose. "ERROR: The ID value "'2023'"n occurs twice in the same BY group." Below is the table I am trying to create in SAS. Below the table visual is the data Output from SAS, and then my code and log code. Can anyone offer suggestions on how to get the desired outcome. I am using a macro that will loop through the Code column in the output data and pull the values for each class code.
proc sql; create table SalesbyEP_Year as
Select distinct * From
(SELECT DISTINCT * FROM SalesTable_2023PY
UNION ALL
SELECT DISTINCT * FROM SalesTable_2023CY)
group by Enrollment_Period, Year, Active_policies
;QUIT;
PROC TRANSPOSE DATA = SalesbyEP_Year OUT= OUTPUT (drop=_name_);
BY Enrollment_period ;
ID Year;
The SAS System
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGHTML TEMP;
15 ODS HTML(ID=EGHTML) FILE=EGHTML
16 ENCODING='utf-8'
17 STYLE=HTMLBlue
18 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
19 NOGTITLE
20 NOGFOOTNOTE
21 GPATH=&sasworklocation
22 ;
NOTE: Writing HTML(EGHTML) Body file: EGHTML
23 FILENAME EGSR TEMP;
24 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
25 STYLE=HTMLBlue
26 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
27 NOGTITLE
28 NOGFOOTNOTE
29 GPATH=&sasworklocation
30 ENCODING=UTF8
31 options(rolap="on")
32 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
33
34 GOPTIONS ACCESSIBLE;
35 PROC TRANSPOSE DATA = SalesbyEP_Year OUT= OUTPUT (drop=_name_);
36 BY Enrollment_period ;
37 ID Year;
38 var Active_policies
39 ;RUN;
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
The SAS System
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
Enrollment_Period=AEP
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
Enrollment_Period=LockIn
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2022'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
The SAS System
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
ERROR: The ID value "'2023'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
Enrollment_Period=OEP
ERROR: All BY groups were bad.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 83 observations read from the data set WORK.SALESBYEP_YEAR.
WARNING: The data set WORK.OUTPUT may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.OUTPUT 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
40
41 GOPTIONS NOACCESSIBLE;
42 %LET _CLIENTTASKLABEL=;
43 %LET _CLIENTPROCESSFLOWNAME=;
44 %LET _CLIENTPROJECTPATH=;
45 %LET _CLIENTPROJECTPATHHOST=;
46 %LET _CLIENTPROJECTNAME=;
47 %LET _SASPROGRAMFILE=;
48 %LET _SASPROGRAMFILEHOST=;
49
50 ;*';*";*/;quit;run;
51 ODS _ALL_ CLOSE;
52
53
54 QUIT; RUN;
55
var Active_policies
;RUN;
When programming, details are extremely important. Your COLUMNS statement misses a crucial comma.
Two questions.
What about the CODE variable in the photograph you posted of the data? Should that be part of the BY group for the proc transpose?
What type of variable is YEAR? Is it numeric or character? If numeric what display format does it have a attached? If YEAR is a date value with the YEAR format attached then two different dates like '01JAN2022'd and '01FEB2022'd will both try to create the same variable named '2022'n . And if year is a character variable then two different values like '2022' and ' 2022' will both try to create the same variable named '2022'n.
Thank you, Tom
The CODE variable in the photograph I posted of the data is not going to be included in the report, instead I am going to use a macro and loop through each code attribute for a seperate report for each one. I appreciate the info on the date's and will double check the values on those and use for future trobleshooting. I used the proc report code you provided and it gets me a lot closer to what I'm look for and it's so much easier. I didn't realize i could skip proc transpose and do everything in proc report. Below is your code and the report I have when I ran it . I want the analysis sum to calulate under the year columns rather than it's own Sales column. Can you tell me how to get the analysis sum under the years?
cols ("Total Sales" enrollment_period sales year);
define Enrollment_Period / "Product" group /*style=[cellwidth=1.4in]
style(column)=[font=("Arial", 8.0pt)]*/ ;
define Year / "Year" across /*style=[cellwidth=1.4in]
style(column)=[font=("Arial", 8.0pt)]*/ ;
define Sales / "Sales" analysis sum /*style=[cellwidth=1.4in]
style(column)=[font=("Arial", 8.0pt)]*/ ;
What you want is not a dataset, but a report.
Use enrollment_period as group, year as an across variable, and sales as analysis with sum.
Something like
proc report data=have;
column ("Total Sales" enrollment_period sales,year);
define enrollment_period / "Product" group;
define sales / "" analysis sum;
define year / "" across;
run;
Untested; for tested code, supply example data in a data step with datalines, so we can recreate your dataset with a simple copy/paste and submit.
Edit: added column-spanning header
Kurt - I used the proc report code you provided and it gets me a lot closer to what I'm look for and it's so much easier. I didn't realize i could skip proc transpose and do everything in proc report. Below is your code and the report I have when I ran it . I want the analysis sum to calulate under the year columns rather than it's own Sales column. Can you tell me how to get the analysis sum under the years?
proc report data=SalesbyEP_Year nowd split='|' spanrows
style(header) = [font=("Arial",9.0pt)
vjust=middle just=center background=%RGB(0,145,204) foreground=whitesmoke font_weight=bold cellwidth=1in];
cols ("Total Sales" enrollment_period sales year);
define Enrollment_Period / "Product" group /*style=[cellwidth=1.4in]
style(column)=[font=("Arial", 8.0pt)]*/ ;
define Year / "Year" across /*style=[cellwidth=1.4in]
style(column)=[font=("Arial", 8.0pt)]*/ ;
define Sales / "Sales" analysis sum /*style=[cellwidth=1.4in]
style(column)=[font=("Arial", 8.0pt)]*/ ;
When programming, details are extremely important. Your COLUMNS statement misses a crucial comma.
you are correct, I just made that adjustment and it gave me what I needed.
Thank you!!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.