Advanced Search

SAS Macro to reorder dataset variables in alphabetical order...

/ Published in: SAS
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. options nosource nomprint nomlogic nosymbolgen;
  2.  
  3. %macro reorder(dsn); 
  4. 	/* Get the variables names to a dataset using proc contents and keeping the variable name only */ 
  5. 	proc contents data=&dsn
  6. 	out=varnames(keep=name) noprint;
  7. 	run;
  8.  
  9. 	/* It is very much important that you UPCASE or LOWCASE the variable names...
  10. 	otherwise you get a different order...Remove this datastep and see for yourself... */ 
  11. 	data varnames;
  12. 		set varnames;
  13. 		name=lowcase(name);
  14. 	run;
  15.  
  16. /* Sort the variable names in alphabetical order */ 
  17. proc sort data=varnames; 
  18. by name;
  19. run;
  20.  
  21. /* Get the observation count */ 
  22.  
  23. data _null_;
  24. 	set varnames nobs=num;
  25. 	call symput('obscnt',num);/* Get the observation count */ 
  26. 	call symput(compress('macvar'||_n_),trim(left(name))); /* Get the variable names into macro variables */ 
  27. run;
  28.  
  29. %let obscnt=&obscnt; /*remove the leading and trailing blankspaces generated when numeric is converted to Best12. format */ 
  30. %put obscnt=&obscnt;
  31. /*Please NOTE that the step of getting all variable names into a macro variable could be simply done by using SQL instead of a macro
  32. proc sql noprint;
  33. select trim(left(name)) into:macvar separated by ' '
  34. from varnames;
  35. quit;
  36.  
  37. and the next datastep simply
  38.  
  39. data &dsn;
  40. retain &macvar;
  41. set &dsn;
  42. run;
  43.  
  44. But the cons here is that if there are too many variables and the total length of all the names put together crosses 32767 bytes the SQL approach would'nt work...
  45. */ 
  46.  
  47.  
  48. data &dsn;
  49. retain %do i=1 %to &obscnt; 
  50.             &&macvar&i  /* NOTE: there should be a blank space after &&macvar&i to separate the variable names by space
  51.                  eg. retain subject a b c; NOTE: NO semicolon should be typed here*/ 
  52.        %end;;
  53. set &dsn;
  54. run;
  55. %mend reorder;
  56.  
  57. /* Example dataset with variety of variable names */ 
  58. data flags; 
  59. set sashelp.flags; 
  60. a=2; 
  61. b=4; 
  62. _common=10; 
  63. Cool=30; 
  64. SubJecT=40; 
  65. run; 
  66.  
  67. ods listing close;
  68. ods html body="C:\Reorder.html";
  69. title 'Order of the Variable Before Re-ordering';
  70. proc contents data=flags; run;
  71.  
  72. %reorder(flags); 
  73.  
  74. title 'Order of the Variable after Re-ordering';
  75. proc contents data=flags; run;
  76. ods html close;
  77. ods listing;

URL: http://sastechies.blogspot.com/2009/11/sas-macro-to-reorder-dataset-variables.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.