Proc Transpose

[AmrAd]

Hi

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

to  

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

[PaigeMiller]

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.

[maguiremq]

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.

[Kurt_Bremser]

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.

[Tom]

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;

[ballardw]

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.