topic Re: Order the Month in Acsending order in proc transpose in SAS Programming

Order the Month in Acsending order in proc transpose

Q1983 — Tue, 16 Nov 2021 16:39:03 GMT

data have;
length
  dept $25
  month_ 4
  count_ 8
  current 8
;
input dept month_ :monyy5. count_ current;
format month_ yymmd7.;
datalines;
Atlantic JAN20 1 1 
Atlantic JAN20 1 0 
Central FEB20 1 1  
Central APR20 1 1   
Central MAR20 1 0 
Pacific APR20 1 0   
Pacific FEB20 1 1
;
run;

PROC TRANSPOSE data=have OUT=have_2 let;
BY dept;
ID month_;
VAR  count_   /*'WAIVED FEE VOLUME'n*/;
RUN;

When the proc transpose runs, it produces the following

dept	_NAME_	2020-01	2020-02	2020-04	2020-03
Atlantic	count_	1
Central	count_		1	1	1
Pacific	count_		1	1

How can I order the for references to Month in ascending order. In this case the 2020-04 should be last.

Re: Order the Month in Acsending order in proc transpose

Kurt_Bremser — Tue, 16 Nov 2021 16:53:42 GMT

In reporting procedures, the order can be determined from the values (with options like ORDER=INTERNAL). TRANSPOSE sets the column names in the order the ID values are encountered, because in datasets column order is irrelevant (columns are found by name, not position).

What do you intend to do with the wide data?

Re: Order the Month in Acsending order in proc transpose

ballardw — Tue, 16 Nov 2021 18:10:41 GMT

Are you sure that you actually need a transposed data set like that? What are you doing with it?

Re: Order the Month in Acsending order in proc transpose

Q1983 — Tue, 16 Nov 2021 19:01:18 GMT

I need to show the numeric month reference in ascending order as shown above. I was given the idea to use order=internal in my proc report so I will try that

Re: Order the Month in Acsending order in proc transpose

Ksharp — Wed, 17 Nov 2021 11:44:00 GMT

No. You need change it after proc transpose.

data have;
length
  dept $25
  month_ 4
  count_ 8
  current 8
;
input dept month_ :monyy5. count_ current;
format month_ yymmd7.;
datalines;
Atlantic JAN20 1 1 
Atlantic JAN20 1 0 
Central FEB20 1 1  
Central APR20 1 1   
Central MAR20 1 0 
Pacific APR20 1 0   
Pacific FEB20 1 1
;
run;

options validvarname=any;

PROC TRANSPOSE data=have OUT=have_2 let;
BY dept;
ID month_;
VAR  count_   /*'WAIVED FEE VOLUME'n*/;
RUN;

proc sql noprint;
select distinct nliteral(put(month_,yymmd7.)) into : monthes separated by ' ' from have ;
quit;
data want;
 retain dept &monthes.;
set have_2;
run;