BookmarkSubscribeRSS Feed
AmrAd
Obsidian | Level 7

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 !!
 

MISDATECUSTNOAdvAmt
30-Nov-221010003077
31-Dec-221010003077
31-Jan-231010003077
28-Feb-231010003077
30-Nov-2210100052050
31-Dec-2210100052050
31-Jan-2310100052050
28-Feb-2310100052050
30-Nov-2210100054550
31-Dec-2210100054550
31-Jan-2310100054550
28-Feb-2310100054550
30-Nov-221010008007

 

to  

CUSTNO30-Nov-2231-Dec-2231-Jan-2328-Feb-23
1010003077777
10100052050505050
10100054550505050
1010008007   
5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
maguiremq
SAS Super FREQ

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
Super User

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
Super User Tom
Super User

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;

 

Tom_0-1678984929127.png

 

ballardw
Super User

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 748 views
  • 3 likes
  • 6 in conversation