BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

3 REPLIES 3
data_null__
Jade | Level 19

Are you using PROC TRANSPOSE?

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

Try sorting by descending date.

Vince28_Statcan
Quartz | Level 8

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

podarum
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1123 views
  • 0 likes
  • 3 in conversation