SAS Macro to split a dataset into multiple datasets vertically with a common primary key


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



Copy this code and paste it in your HTML
  1. /*
  2. Taking an example...
  3.  
  4.  
  5. */
  6.  
  7. data flags;
  8. set sashelp.flags;
  9. run;
  10.  
  11. /**
  12. To be called like this...
  13. %splitdsnverticallykey(dsn,varperdsn,keyvars=);
  14. eg. %splitdsnverticallykey(sashelp.vtable,4,keyvars=memname libname);
  15. Where -----------
  16. dsn - libname.datasetname to be split
  17. varperdsn - How many vars per dsn excluding the key variables
  18. keyvars - specify the primary key variables
  19. */
  20.  
  21.  
  22.  
  23. %macro splitdsnverticallykey(dsn,varperdsn,keyvars=);
  24.  
  25. /* split the keyvars into individual macro var names*/
  26. %let num=1;
  27. %let keyvar=%scan(&keyvars,&num,' ');
  28. %let keyvar&num=&keyvar;
  29. %let keyvarstr=%str(%")&keyvar%str(%",);
  30.  
  31. %do %while(&keyvar ne );
  32. %let num=%eval(&num + 1);
  33. %let keyvar=%scan(&keyvars,&num,' ');
  34. %let keyvar&num=&keyvar;
  35. %if &keyvar ne %then %let keyvarstr=&keyvarstr%str(%")&keyvar%str(%",);
  36. %end;
  37.  
  38. %let numkeyvars=%eval(&num - 1);
  39. %let keyvarstr=%substr(&keyvarstr,1,%length(&keyvarstr)-1);
  40.  
  41. data _null_;
  42. /*Open the dataset and assign to handler*/
  43. dsid=open("&dsn","i");
  44.  
  45. /*attrn with nvars gives u the count of variables */
  46. numofvars=attrn(dsid,"nvars");
  47. call symput('numofvars',numofvars-&numkeyvars);
  48.  
  49. /*identify total number of dsns would it fit excluding the key vars*/
  50. totalnumdsns=ceil((numofvars-&numkeyvars)/&varperdsn);
  51.  
  52. call symput('totalnumdsns',totalnumdsns);
  53.  
  54. do i=1 to numofvars;
  55.  
  56. varname=trim(left(varname(dsid,i)));
  57. if varname not in (&keyvarstr) then
  58. do;
  59. k+1;
  60. /*Get the name of the variables into macro variables*/
  61. call symput(compress('varname'||k),varname);
  62. end;
  63. end;
  64. rc=close(dsid);
  65. run;
  66.  
  67. %let totalnumdsns=&totalnumdsns;
  68. %let numofvars=&numofvars;
  69. %put The dataset &dsn with &numofvars of variables excluding variables {&keyvars} is split vertically into &totalnumdsns datasets;
  70.  
  71. /* name the datasets in sequence */
  72.  
  73. %let start=0;
  74. %let end=0;
  75.  
  76. %do i=1 %to &totalnumdsns;
  77. %let start=%eval((&i-1)*&varperdsn + 1);
  78. %let end=%eval(&start + &varperdsn - 1);
  79. %if &end ge &numofvars %then %let end=&numofvars;
  80.  
  81. %put start=&start end=&end;
  82.  
  83. data &dsn.&i; /*Note: There should be a blank after &dsn.&totalnumdsns*/
  84. retain &keyvars;
  85. set &dsn (keep=&keyvars
  86. %do m=&start %to &end;
  87. &&varname&m.
  88. %end;);
  89. run;
  90. %end;
  91.  
  92. %mend splitdsnverticallykey;
  93.  
  94. options nosource;
  95. %splitdsnverticallykey(flags,4,keyvars=title);

URL: http://sastechies.blogspot.com/2010/01/sas-macro-to-split-dataset-into.html

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.