topic Proc Transpose in SAS Programming

Proc Transpose

saikiran_nemani — Sun, 16 Apr 2023 12:25:27 GMT

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

Re: Proc Transpose

PaigeMiller — Sun, 16 Apr 2023 12:19:42 GMT

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.

Re: Proc Transpose

saikiran_nemani — Sun, 16 Apr 2023 13:44:47 GMT

I have updated it and please help me on this

Re: Proc Transpose

Tom — Sun, 16 Apr 2023 14:34:38 GMT

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;

Re: Proc Transpose

PaigeMiller — Sun, 16 Apr 2023 20:07:27 GMT

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.

Re: Proc Transpose

Kurt_Bremser — Sun, 16 Apr 2023 19:59:48 GMT

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