SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Selecting a range of columns in SAS

Reply
Occasional Contributor
Posts: 8

Selecting a range of columns in SAS

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!

Frequent Contributor
Posts: 108

Re: Selecting a range of columns in SAS

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;
Occasional Contributor
Posts: 8

Re: Selecting a range of columns in SAS

Posted in reply to pearsoninst
thanks for your time Smiley Happy
Valued Guide
Posts: 634

Re: Selecting a range of columns in SAS

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;

 

 

Valued Guide
Posts: 634

Re: Selecting a range of columns in SAS

I couldn't help myself.  Smiley Happy

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)

Occasional Contributor
Posts: 8

Re: Selecting a range of columns in SAS

Thank you so much for the effort you put on this Smiley Happy
Super User
Super User
Posts: 7,970

Re: Selecting a range of columns in SAS

Super User
Posts: 5,430

Re: Selecting a range of columns in SAS

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.
Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 594 views
  • 4 likes
  • 5 in conversation