topic Re: Proc Transpose in SAS Programming

Proc Transpose

AmrAd — Thu, 16 Mar 2023 09:27:11 GMT

I am trying to transpose below table from however i receive this error "ERROR: The ID value "'30NOV2022'n" occurs twice in the same BY group."

Any Help !!

MISDATE	CUSTNO	AdvAmt
30-Nov-22	101000307	7
31-Dec-22	101000307	7
31-Jan-23	101000307	7
28-Feb-23	101000307	7
30-Nov-22	101000520	50
31-Dec-22	101000520	50
31-Jan-23	101000520	50
28-Feb-23	101000520	50
30-Nov-22	101000545	50
31-Dec-22	101000545	50
31-Jan-23	101000545	50
28-Feb-23	101000545	50
30-Nov-22	101000800	7

CUSTNO	30-Nov-22	31-Dec-22	31-Jan-23	28-Feb-23
101000307	7	7	7	7
101000520	50	50	50	50
101000545	50	50	50	50
101000800	7

Re: Proc Transpose

PaigeMiller — Thu, 16 Mar 2023 10:13:05 GMT

This layout makes the data very difficult to work with. Better to not transpose and work with the data long (most SAS PROCs expect the data in long layout anyway). What will you be doing with this data?

In addition, SAS data set names cannot have dashes in them. Picking the last day of the month is hard to code without making mistakes. These dates will not sort in any reasonable order.

Re: Proc Transpose

maguiremq — Thu, 16 Mar 2023 12:17:13 GMT

Hi @AmrAd, on top of @PaigeMiller's comments - you have two values for that customer on that date, but your transpose only contains one value (50) for that date.

Do you consider that a duplicate? If so, I would suggest de-duplicating before running PROC TRANSPOSE.

Re: Proc Transpose

Kurt_Bremser — Thu, 16 Mar 2023 16:26:52 GMT

First, make a decision how to deal with duplicates. Do you want the sum, the average, minimum or maximum in such a case?

Your intended "dataset" looks more like a report for human consumption. For such, you use PROC REPORT, where misdate is used as an ACROSS variable, and where the calculation (see above) can be done right in the procedure.

Re: Proc Transpose

Tom — Thu, 16 Mar 2023 16:42:35 GMT

That looks like a REPORT and not a dataset. You can easily make such a report using PROC REPORT.

data have;
  input MISDATE :date. CUSTNO :$10. AdvAmt ;
  format misdate yymmdd10. ;
cards;
30-Nov-22 101000307 7
31-Dec-22 101000307 7
31-Jan-23 101000307 7
28-Feb-23 101000307 7
30-Nov-22 101000520 50
31-Dec-22 101000520 50
31-Jan-23 101000520 50
28-Feb-23 101000520 50
30-Nov-22 101000545 50
31-Dec-22 101000545 50
31-Jan-23 101000545 50
28-Feb-23 101000545 50
30-Nov-22 101000800 7
;

proc report data=have;
 columns custno advamt,misdate;
 define custno/group;
 define misdate / across ' ';
 define advamt / sum ' ' ;
run;

Re: Proc Transpose

ballardw — Thu, 16 Mar 2023 16:41:50 GMT

At least show your code, better the entire log with the code and messages.

What do you expect to do with the transformed data? Do you expect to have two columns with the same name when there are duplicate MISDATE (apparently) values for a Custno (guessing)? Since you have duplicates you need to show us what you expect to get with the duplicates, so include some in the have and want.