topic Re: How to solve for proc transpose error in SAS Programming

How to solve for proc transpose error

LMSSAS — Thu, 02 Feb 2023 21:52:27 GMT

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;

Re: How to solve for proc transpose error

Tom — Thu, 02 Feb 2023 22:19:42 GMT

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.

Re: How to solve for proc transpose error

Kurt_Bremser — Fri, 03 Feb 2023 09:02:28 GMT

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

Re: How to solve for proc transpose error

LMSSAS — Fri, 03 Feb 2023 13:51:04 GMT

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)]*/ ;

Re: How to solve for proc transpose error

LMSSAS — Fri, 03 Feb 2023 14:02:55 GMT

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)]*/ ;

Re: How to solve for proc transpose error

Kurt_Bremser — Fri, 03 Feb 2023 15:24:08 GMT

When programming, details are extremely important. Your COLUMNS statement misses a crucial comma.

Re: How to solve for proc transpose error

LMSSAS — Fri, 03 Feb 2023 15:26:44 GMT

you are correct, I just made that adjustment and it gave me what I needed.

Thank you!!