Help using Base SAS procedures

sort columns from lft to right after proc transpose

Reply
Super Contributor
Posts: 395

sort columns from lft to right after proc transpose

I have a table that updates on a monthly basis with new monthly info... after I transpose the data where the months are now columns, is there a way I can have the latest month in the most left side (after the index variables), and every month keep on shifting righward ?  Right now the months get updates from right to left .  (Don't want to use retain becasue there are actually many months, just showing 3 for vewing) .Thanks

Now

Var1    Sep2013   Oct2013    Nov2013

Tor           345          346             361

Next Month

Var1    Dec2013   Nov2013   Oct2013    Sep2013

Tor          456             361          346            345

Respected Advisor
Posts: 3,773

Re: sort columns from lft to right after proc transpose

Are you using PROC TRANSPOSE?

If so you need to understand how the new variables are defined.

Try sorting by descending date.

Super Contributor
Posts: 339

Re: sort columns from lft to right after proc transpose

Transpose creates variables in the order they are encountered in the variable(s) used to create new ones after the transpose. There are different approaches to tackle the problem depending on how large a file and how long it takes to process.

You could consider adding a "dummy" record to your untransposed file at the very beginning of the file recreating the desired dates in descending order instead of the ascending order it naturally gets updated. You could also proc sort descending or order by desc on your date variable before transposing.

Ultimately, you could use the transposed dataset and recreate a new dataset with the variables in a different order after the shot. This can be done with some macros, the dictionary tables and a keep statement or really anything that will make the data step encounter the variables name in the order you want them to be before the set statement. I will only examplify this very case simply as its the only one that requires some more complex programming statements.

proc sql;

     select name

     into :dt1-:dt9999    /* generic upper boundary on the number of date variables - only the necessary macro vars will be created */

     from sashelp.vcolumn

     where     libname="WORK" /* or whatever library your NOW dataset is stored in */

               and memname = "NOW"

               and prxmatch("m/^[A-Za-z]{3}[0-9]{4}/", name)>0 /* validates that the variable name is following 3 char 4 digit patterns. If you have other odd variable names you could be more specific and build a list of 3 letter prefixes                                                                                                     corresponding to months or do further year processing in the regular expression. I assumed it was not required */

     ;

quit;

%let ndates=&sqlobs; /* to be able to loop on dt1-dt&sqlobs. after other subsequent proc sql */

proc sql;

     select name

     into :leads separated by ' '

     from sashelp.vcolumn

     where     libname="WORK" /* or whatever library your NOW dataset is stored in */

               and memname = "NOW"

               and prxmatch("m/^[A-Za-z]{3}[0-9]{4}/", name)=0 /* other non-match variables will be put in front of all date vars, you can change this rule if only a few specific variables are to be leads that is merely my assumption of your                                                                                                intentions */

     ;

quit;

%macro write();

data NEXT_MONTH;

     keep &leads.

     %do i=&ndates. %to 1 %by -1;

     &&dt&i..

     %end;

     ; /* to close the keep statement */

     set NOW;

run;

%mend;

%write();

This is all untested so there might be some syntax errors but the idea is all there. Please note that SASHELP.VCOLUMN is naturally sorted by column number hence the reverse %do loop. There are alternative approaches like grouping and ordering inside the proc sql "descending" and then doing a regular %do loop

Super Contributor
Posts: 395

Re: sort columns from lft to right after proc transpose

Thanks guys, sorting before transpose with descending worked perfect...Smiley Happy

Ask a Question
Discussion stats
  • 3 replies
  • 235 views
  • 0 likes
  • 3 in conversation