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 |
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.
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.
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.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.