Proc Transpose

[saikiran_nemani]

I am using PROC Transpose and following error occurs ID values occurs twice same in the BY group.

Proc SORT DATA=TEST OUT=TEST1;

BY DATE;

RUN;

Proc Transpose data=test1 out = test2;

BY DATE;

ID DATE;

VAR APPLICANT_NO Days_Past_Due;

RUN;

Applicant_No	Days_Past_Due	Date
1	57	01Nov2019
1	57	01Dec2019
1	58	01Jan2020
1	56	01Feb2020
1	56	01Mar2020
1	86	01Apr2020
1	57	01Apr2021
1	57	01May2021
1	87	01Jun2021
1	88	01Jul2021
1	88	01Aug2021
1	88	01Sep2021
2	57	01Mar2020
2	57	01Apr2020
2	87	01Apr2021
2	88	01May2021
2	88	01Jun2021
2	88	01Jul2021
2	87	01Aug2021
2	88	01Sep2021

Sample Expecting output like below.

Applicant_No	01-Nov-19	01-Dec-19	01-Jan-20	01-Feb-20	01-Mar-20
1	57	57	58	56	56

[PaigeMiller]

What happens if you remove

id date;

from your code? Does it work then?

But basically, we don't really know what the desired output it. Can you show us what you want data set TEST2 to look like?

Also, you can't have a variable named APPLICANT NO and then refer to it as

VAR APPLICANT NO DPD;

because there can't be spaces in SAS variable name under normal operation. You could turn on an option that allows spaces in variable names, but you still can't use the above command as written. And you don't have a variable named DPD.

[saikiran_nemani]

I have updated it and please help me on this

[PaigeMiller]

It is an awful idea to have dates as variable names. Nor is there any easy way to get the dates into calendar order, they will most likely appear in alphabetical order.

How about using PROC REPORT rather than PROC TRANSPOSE? Assuming you are using numeric date values (are you?) then you can do this

proc report data=test1;
    columns applicant_no date,days_past_due;
    define applicant_no/group "Applicant #";
    define date/across "Date" order=internal format=monyy7.;
    define days_past_due/sum "Days Past Due";
run;

and your dates will be in calendar order, not alphabetical order.

[Tom · Accepted solution]

Your input has more than one applicant.  So use APPLICANT_NO in the BY statement instead of DATE.

proc SORT DATA=TEST OUT=TEST1;
  BY APPLICANT_NO DATE;
RUN;
proc transpose data=test1 out = test2;
  BY APPLICANT_NO ;
  ID DATE;
  VAR Days_Past_Due;
RUN;

[Kurt_Bremser]

You want a report, not a dataset. Use PROC REPORT, as already suggested.