Advanced Search

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

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

Expand | Embed | Plain Text

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.