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
Are you using PROC TRANSPOSE?
If so you need to understand how the new variables are defined.
Try sorting by descending date.
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
Thanks guys, sorting before transpose with descending worked perfect...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.