topic Re: Selecting a range of columns in SAS in SAS Data Management

Selecting a range of columns in SAS

Sepehrp — Wed, 04 Nov 2015 17:32:32 GMT

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!

Re: Selecting a range of columns in SAS

pearsoninst — Wed, 04 Nov 2015 18:24:21 GMT

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;

Re: Selecting a range of columns in SAS

ArtC — Wed, 04 Nov 2015 18:29:56 GMT

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;

Re: Selecting a range of columns in SAS

ArtC — Wed, 04 Nov 2015 20:11:10 GMT

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)

Re: Selecting a range of columns in SAS

RW9 — Thu, 05 Nov 2015 10:25:26 GMT

Dupicate post:

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

Re: Selecting a range of columns in SAS

Sepehrp — Thu, 05 Nov 2015 18:17:41 GMT

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

Re: Selecting a range of columns in SAS

Sepehrp — Thu, 05 Nov 2015 18:19:59 GMT

thanks for your time 🙂

Re: Selecting a range of columns in SAS

LinusH — Thu, 05 Nov 2015 18:39:56 GMT

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.