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.
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;
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?
Are you sure that you actually need a transposed data set like that? What are you doing with it?
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
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.