Selecting a range of columns in SAS

[Sepehrp]

Hello everyone, I have a data set with about 900 columns in it. I want to export this data set into access, since access cannot handle more than 255 variables, I want to split the data set into four data sets with less than 255 columns. I was wondering is there a way to select a range of columns in sas? Thanks!

[pearsoninst]

Try this ..

data have;
input x1 x2 y1 y2 y3 ;
datalines;
1 2 5 8 10
3 4 7 9 12
;
run;
data  want1;
set have;
drop x1-x2;
run;
data want2;
set have;
drop y1-y3;
run;

[Sepehrp]

thanks for your time 🙂

[ArtC]

You can also select columns using other naming shortcuts:

Select those variables starting with the prefix X

  keep x:;

select all numeric or all character variables

  keep _numeric_;

  keep _character_;

Select by naming variables with a common prefix and a numeric suffix

  keep month1 - month12;

[ArtC]

I couldn't help myself. 

Here is a macro that will split the data set into a user specified number of data sets.

%macro split(dsn=, dsnroot=fred, splitcnt=3);
%local i j;
proc contents data=&dsn
              out=vlist(keep=name)
              noprint;
   run;

proc sql noprint;
   select name
      into :vars1 -
         from vlist;
   %let vcnt = &sqlobs;
   quit;

%* Nominal number of variables in each new dataset;
%let cnteach = %sysevalf(&vcnt/&splitcnt,ceil);

data
   %do i = 1 %to &splitcnt;
      &dsnroot&i(keep=
         %do j= %sysfunc(max(1,%eval((&i-1)*&cnteach+1))) %to %sysfunc(min(&vcnt,%eval((&i)*&cnteach)));
           &&vars&j
           %put &=i &=j;
         %end; )
   %end;
   ;
   set &dsn;
   run;
%mend split;

%split(dsn=sashelp.shoes, dsnroot=want, splitcnt=2)

[Sepehrp]

Thank you so much for the effort you put on this 🙂

[RW9]

Dupicate post:

https://communities.sas.com/t5/Base-SAS-Programming/bd-p/programming

[LinusH]

How do you even handle so many columns? And how will the user's in Access use this data now that's even split into separate tables? Normalizing (probably some transposing included) is probably a better way to maintain and report on this data.